Return unknown resultset from a function

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

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