Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
The above will only work if the fields returned from the tables are indentical both in name and number of fields. Is it possible to run multiple SQL selects against tables with different structures, append the results of each of these queries to the same variable and then at the end output the contents of this variable to a text file? –  John Mitchell Jan 17 '13 at 1:31
    
@JohnMitchell: Almost anything is possible. You just need to provide more detail in your questions (like a test case and example output). I have added an example to output rows from tables with different structure. –  Erwin Brandstetter Jan 17 '13 at 1:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.