Thread: Return unknown resultset from a function
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. Regards Jan
Hello 2012/3/4 Jan Meyland Andersen <jma@agile.dk>: > 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? > no, or it is not possible simply. PostgreSQL is strongly typed. I know only one workaround - using refcursor http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html Regards Pavel Stehule > I also tried to return the data as a text array but I also have trouble with > that. > > Regards > > Jan > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Mar 4, 2012, at 14:52, 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. > > Regards > > Jan > Try HSTORE. You can return RECORD from the function but the SQL user still has to define the names/types at design time. Text array should work but you lose column names which HSTORE maintains. You could also return a delimited string and then split it in the calling routine. David J
On 04/03/12, 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. There is a section on this in the docs at (for instance) http://www.postgresql.org/docs/8.4/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS A specific example which may help is CREATE FUNCTION new_emp() RETURNS emp AS $$ SELECT ROW('None', 1000.0, 25, '(2,2)')::emp; $$ LANGUAGE SQL; Note that there are two ways of calling such a function. You probably want the "SELECT *" form. Rory -- Rory Campbell-Lange rory@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928
On 04/03/12, Rory Campbell-Lange (rory@campbell-lange.net) wrote: > On 04/03/12, Jan Meyland Andersen (jma@agile.dk) wrote: > > 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? > There is a section on this in the docs at (for instance) > http://www.postgresql.org/docs/8.4/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS > > A specific example which may help is > > CREATE FUNCTION new_emp() RETURNS emp AS $$ > SELECT ROW('None', 1000.0, 25, '(2,2)')::emp; > $$ LANGUAGE SQL; > > Note that there are two ways of calling such a function. You probably > want the "SELECT *" form. My apologies -- if you aren't sure about the return type you will need to use a RECORD. eg http://www.postgresql.org/docs/7.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS There is a good answer on stack overflow about returning polymorphic results here: http://stackoverflow.com/questions/4547672/how-to-return-multiple-fields-as-a-record-in-postgresql-pl-pgsql -- Rory Campbell-Lange rory@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928
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
Merlin Moncure <mmoncure@gmail.com> writes:
> 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.
Hmm, couldn't you do a FETCH into a record variable in plpgsql? Not
that you'd not have problems manipulating the record variable, since
plpgsql is pretty strongly typed itself.
regards, tom lane
On Mon, Mar 5, 2012 at 10:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure@gmail.com> writes: >> 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. > > Hmm, couldn't you do a FETCH into a record variable in plpgsql? Not > that you'd not have problems manipulating the record variable, since > plpgsql is pretty strongly typed itself. Yeah -- good point on both sides -- you can do it, but it's pretty limiting: you can only fetch a row at a time and the result data can't be further expressed in another query. A CTE based FETCH has been suggested a couple of times as a hypothetical workaround. Whether the data is processed on the server or the client the result essentially the result is the same...you're forced into a highly iterative method of programming that I try to avoid whenever possible. TBH though I find the textual workarounds to the type system to work pretty well, meaning that most of the useful things which were historically only possible in C have been nicely wrapped or seem to be just plain impossible (like handling mixed type variadic functions, or receiving generic RECORDs as arguments). merlin