On Sun, Mar 4, 2012 at 1:52 PM, Jan Meyland Andersen <jma@agile.dk> wrote:
> How do I return an unknown resultset from a function
>
> My main problem is that I do not know how many columns or the data type of
> the columns before runtime.
> It this possible at all?
>
> I also tried to return the data as a text array but I also have trouble with
> that.
The basic deal with postgres is that while it's legal to return
untyped RECORDSs from functions, only C functions can do that. Also,
when calling C functions you still have to decorate the returned
record with types when the query is called. For a couple of good
examples of that see (\d+) the pg_locks view which wraps
pg_lock_status() record returning function or check out dblink which
makes heavy use of record returning functions.
The only exception to this rule is cursors. Reading from cursors via
FETCH allows you to pull data from a refcursor that was set up in a
previous function call and works pretty well, but comes with the giant
downside that the results can be directed only to the client.
For pure server-side manipulation of untyped structures you have to
flatten everything to text. You can do it yourself:
CREATE OR REPLACE FUNCTION get_records(table_name TEXT) RETURNS SETOF TEXT AS
$$
DECLARE
query TEXT;
BEGIN
query = format('SELECT %s::text FROM %s', table_name, table_name);
RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE PLPGSQL STABLE;
select get_records('foo');
Once you have the record in text representation you can throw it
around until it has to get casted back to 'foo' record type:
select (get_records('foo')::foo).*;
You can also do lots of wonderful things with the hstore type, or the
non plpgsql server-side languages (which basically flatten everything
to text).
merlin
