Postgresql – Save output from multiple SQL SELECT commands to a file

bulkcopydatafileplpgsqlpostgresql

Within a Postgres function I would like to run a number of SQL SELECT commands and append the output to the same variable. After all the SELECT commands have run this output should be written to a file so that the user could read this report.

What is the best way to implement this?

Best Answer

Basic function to return rows from multiple SELECT statements:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS TABLE(a_id integer, txt text) AS
$func$
BEGIN

RETURN QUERY SELECT a.a_id, a.txt FROM tbl_a a;

RETURN QUERY SELECT b.a_id, b.txt FROM tbl_b b;

END
$func$ LANGUAGE plpgsql;

You could do the same with UNION ALL.

Simple & quick demo to output from tables with different structure:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS SETOF text AS
$func$
BEGIN

RETURN QUERY SELECT t::text FROM tbl_a t;

RETURN QUERY SELECT t::text FROM tbl_c t;

END
$func$ LANGUAGE plpgsql;

You can cast any type to text in PostgreSQL, even the composite type of a table row ...

Call:

SELECT * FROM f_get_rows()

To write this to a file (local to the server):

COPY (SELECT * FROM f_get_rows()) TO '/path/to/my/file.csv';

To get you started read these chapters in the manual:

For a more specific answer you need to write a more specific question.

Related Question