How to get postgres (8.4) query results with unknown columns

Question

Edit: After posting I found Erwin Brandstetter's answer to a similar question. It sounds like in 9.2+ I could use the last option he listed, but none of the other alternatives sound workable for my situation. However, the comment from Jakub Kania and reiterated by Craig Ringer suggesting I use COPY, or \copy, in psql appears to solve my problem.

My goal is to get the results of executing a dynamically created query into a text file.

The names and number of columns are unknown; the query generated at run time is a 'pivot' one, and the names of columns in the SELECT list are taken from values stored in the database.

What I envision is being able, from the command line to run:

$ psql -o "myfile.txt" -c "EXECUTE mySQLGeneratingFuntion(param1, param2)"

But what I'm finding is that I can't get results from an EXECUTEd query unless I know the number of columns and their types that are in the results of the query.

create or replace function carrier_eligibility.createSQL() returns varchar AS
$$
begin
return 'SELECT * FROM carrier_eligibility.rule_result';
-- actual procedure writes a pivot query whose columns aren't known til run time
end
$$ language plpgsql

create or replace function carrier_eligibility.RunSQL() returns setof record AS
$$
begin 
return query EXECUTE carrier_eligibility.createSQL();
end
$$ language plpgsql

-- this works, but I want to be able to get the results into a text file without knowing
-- the number of columns 
select * from carrier_eligibility.RunSQL() AS (id int, uh varchar, duh varchar, what varchar)

Using psql isn't a requirement. I just want to get the results of the query into a text file, with the column names in the first row.

Have you considered COPY? http://stackoverflow.com/a/1517692/2115135 — Jakub Kania, Apr 14 '13 at 21:39
I hadn't considered that; COPY only writes out to a filesystem available to the server which is might be a showstopper. And the real query is ~ 3000 characters long, I don't know if I can form and execute a COPY command in the server, or pass one that long in from psql. But thanks for the idea. — Levin Magruder, Apr 14 '13 at 22:04
@LevinMagruder `COPY` is server-side, but you can use `COPY ... TO STDOUT` or the convenient `psql` wrapper command `\copy` to write to client-side files. Command size is not an issue. — Craig Ringer, Apr 14 '13 at 23:16
*"I can't get results from an EXECUTEd query unless I know the number of columns and their types that are in the results of the query"* . Details? Error message? — Craig Ringer, Apr 14 '13 at 23:38
I'll try the approach of using \copy thru psql. As for error messages, in the code I posted, if the last line is "select * from carrier eligibility.RunSQL()" (which is what I'd like to be able to run), I get "ERROR: a column definition list is required for functions returning "record"". There are several other forms for EXECUTE but each runs into the same issue: you have to give a literal definition of the structure, or an instance of a table or type having the structure, to EXECUTE. Of course if you know I'm wrong, tell me what to change. — Levin Magruder, Apr 15 '13 at 00:00
Jakuband Craig, \copy does in fact work for me, if you want to put it up as an answer, assuming nothing better is proposed, I'll accept. — Levin Magruder, Apr 15 '13 at 03:42

score 0 · Answer 1 · answered May 16 '13 at 05:14

What format of a text file do you want? Something like csv?

How about something like this:

CREATE OR REPLACE FUNCTION sql_to_csv(in_sql text) returns setof text
SECURITY INVOKER -- CRITICAL DO NOT CHANGE THIS TO SECURITY DEFINER 
LANGUAGE PLPGSQL AS
$$
DECLARE t_row RECORD;
        t_out text;
BEGIN
  FOR t_row IN EXECUTE in_sql LOOP
     t_out := t_row::text;
     t_out := regexp_replace(regexp_replace(t_out, E'^\\(', ''), E'\\)$', '');
     return next t_out;
  END LOOP;
END; 
$$;

This should create properly quoted csv strings without the header. Embedded newlines may be a problem but you could write a quick Perl script to connect and write the data or something.

Note this presumes that the tuple structure (parenthesized csv) does not change with future versions, but it currently should work with 8.4 at least through 9.2.

How to get postgres (8.4) query results with unknown columns

1 Answers1