0

Within a Postgres function I would like to run a number of SELECT commands. For each SELECT command I would like to output written to separate text files.

What is the best way to implement this?

PostgreSQL version: 9.3.5.0

 BEGIN

 RETURN QUERY SELECT text 
    FROM eightks
    WHERE other_events = true 
    AND text ~* '(\y(chief executive officer)\y)'
    AND text ~*'(\y(cancer)\y)'          
    \o /Users/XXXX/desktop/test.txt;

 RETURN QUERY SELECT text
    FROM eightks
    WHERE other_events = true 
    AND text ~* '(\y(chief executive officer)\y)'
    AND text ~* '(\y(sudden)\y)' 
    \o /Users/xxxx/desktop/sudden.txt;

  END

Here is the follow ERROR message

    edgar=# \i test3.sql;
    psql:test3.sql:21: ERROR:  syntax error at or near "RETURN"
    LINE 2: RETURN QUERY SELECT text 
    ^

Here is also the structure of the table if it helps;

     edgar=# \d eightks
           Table "public.eightks"
       Column    |   Type   | Modifiers 
   --------------+----------+-----------
    id           | integer  | 
    doc_id       | bigint   | 
    text         | text     | 
    tsv          | tsvector | 
    other_events | boolean  |

Thanks very much

1 Answer 1

2

You are confusing PL/PgSQL with SQL.

You do not need the RETURN QUERY, nor is it valid in normal SQL.

Simply SELECT text FROM eightks ...

Additionally, if you want to direct output to a particular file with psql's \o, you must run the \o command before the statement you want to write the output for.

Finally - I wonder if you should be looking into the \copy command, if you're trying to write query output to files.

\copy (SELECT ....) TO '/some/file' WITH (FORMAT CSV)
2
  • Yep. COPY all the way. Commented Sep 12, 2014 at 4:48
  • 1
    Also worth noting that from within a function (if it is really needed in this case) one cannot split the output to separate files (well, no file handling at all, unless one goes pl/python or something). A function has only one result set, regardless how many statements produce the output. Commented Sep 12, 2014 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.