Postgresql – Run and Save multiple SELECT statements

postgresqlselect

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

Best Answer

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)
Related Question