Save output from multiple SQL SELECT commands to a file

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:

• CREATE FUNCTION
• Returning From a Function
• COPY

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