CODEWITHSUNDEEP
postgresqldynamic-sql
Levin
Levin

Reputation: 2015

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

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.

Upvotes: 1

Views: 974

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 0

Related Questions