Thread: How does one return rows from plpgsql functions?

    I seemed to have hit a brick wall in some development work with
pgsql. I want to write a plpgsql function that returns a single row of
table data, i.e. a set of fields. Now, from the documentation, I was able
to figure out that one puts the name of the table that defines the row I
want to return as the return data type. I then declare a variable of
that table's row type, and assign it using a SELECT INTO ..., and then
return that variable. This is roughly the structure I am using:

CREATE FUNCTION update_get (int) RETURNS simple AS '
  DECLARE
    ident ALIAS for $1;
    data simple%ROWTYPE;
  BEGIN
    SELECT INTO data * FROM simple WHERE id = ident;
    RETURN data;
  END;
' LANGUAGE 'plpgsql';

The table 'simple' just has a few simple fields (int, text, and
timestamp). When I execute this function, "SELECT update_get(1);" (and
there is a row with id = 1 in that table), I get back a single, large
number (an oid?):

 update_them
-------------
 2197312
(1 row)

Instead of the set of fields that make up a row of table 'simple'. How do
I get the field data? I tried "SELECT update_get(1).id;" but that gives a
syntax error. And "SELECT id(update_get(1));" rewards me with a backend
crash. :( What am I missing here? Thank you in advance for your help.

    PS. This is pgsql 7.1.0 on a Sparc 20 running Debian 2.2 (potato).

    PPS. I tried to search the mailing list archives first, but
fts.postgresql.org always gives me an under construction error on every
search.

---------------------------------------------------------------------------
|   "For to me to live is Christ, and to die is gain."                    |
|                                            --- Philippians 1:21 (KJV)   |
---------------------------------------------------------------------------
|   Ryan Kirkpatrick  |  Boulder, Colorado  |  http://www.rkirkpat.net/   |
---------------------------------------------------------------------------

Ryan Kirkpatrick <pgsql@rkirkpat.net> writes:
> I get the field data? I tried "SELECT update_get(1).id;" but that gives a
> syntax error.

Yeah, that's something we ought to allow.  Maybe for 7.3.

> And "SELECT id(update_get(1));" rewards me with a backend
> crash. :(

Works okay for me in current sources.  Time to update ...

            regards, tom lane

On Sat, 19 Jan 2002, Tom Lane wrote:

> Ryan Kirkpatrick <pgsql@rkirkpat.net> writes:
> > And "SELECT id(update_get(1));" rewards me with a backend
> > crash. :(
>
> Works okay for me in current sources.  Time to update ...

    Okay, once I updated to 7.2b5, that select statement started
working just fine for me. :)
    Though I have hit another problem, if I want to display multiple
fields from the returned row, the function executes again for each
field. I.e. 'SELECT id(update_get(1), fielda(update_get(1), ...' will
result in both the values for id and fielda being displayed, but will
execute update_get() twice. This is not only inefficient, but also
undesireable (esp if update_get() modified something that was only to be
modified once for a single row reterival).
    So, how do I get back and access the entire row returned from a
function without calling it multiple times? Or is that even possible?
Thanks.

---------------------------------------------------------------------------
|   "For to me to live is Christ, and to die is gain."                    |
|                                            --- Philippians 1:21 (KJV)   |
---------------------------------------------------------------------------
|   Ryan Kirkpatrick  |  Boulder, Colorado  |  http://www.rkirkpat.net/   |
---------------------------------------------------------------------------

Ryan Kirkpatrick <pgsql@rkirkpat.net> writes:
>     So, how do I get back and access the entire row returned from a
> function without calling it multiple times?

You don't.  The syntax just doesn't support it, and no I don't want to
put in common-subexpression recognition.

We have talked about functions as table sources:

    SELECT t.col1, t.col2 FROM myfunction(yaddayadda) AS t;

in which presumably the function would only be invoked once (per row?).
Alex Pilosov was working on this, IIRC, but I dunno when it will be done.

            regards, tom lane

On Mon, Jan 21, 2002 at 08:02:46AM -0700, Ryan Kirkpatrick wrote:
>     So, how do I get back and access the entire row returned from a
> function without calling it multiple times? Or is that even possible?
> Thanks.

As I known, their is no straight way to do it. But you can do the
following:

SELECT get_id(p), get_fielda(p) WHERE p = update_get(1);

Here `update_get' allocates space in a memory context like
`CurrentTransactionContext' or what ever is necessary in your case,
puts the resulting typle into the allocated space and returns a
pointer p to that tuple. `get_*' are accessor methods for such a
pointer, accessing the allocated memory and retrieving the values
needed.

To be as type-safe as possible I would recommend to introduce a
special type `PointerToMyTableRow'. The input and output functions of
that type should simply throw an exception (i.e. call
`elog(ERROR,...)'), to avoid that pointers can be externalized.

This all obviously requires some C-coding. But it works and would
solve your problem.


--
Holger Krug
hkrug@rationalizer.com

On Mon, Jan 21, 2002 at 07:16:24PM +0100, Holger Krug wrote:
> SELECT get_id(p), get_fielda(p) WHERE p = update_get(1);

Sorry this was wrong. Obviously it should be:

 SELECT get_id(t.p), get_fielda(t.p) FROM (SELECT update_get(1) as p) as t;

resp.:

 SELECT get_id(p), get_fielda(p) FROM (SELECT update_get(1) as p) as NN;

--
Holger Krug
hkrug@rationalizer.com

On Mon, 21 Jan 2002, Holger Krug wrote:

> On Mon, Jan 21, 2002 at 08:02:46AM -0700, Ryan Kirkpatrick wrote:
> >     So, how do I get back and access the entire row returned from a
> > function without calling it multiple times? Or is that even possible?
> > Thanks.
>
> As I known, their is no straight way to do it. But you can do the
> following:
>
>  SELECT get_id(t.p), get_fielda(t.p) FROM (SELECT update_get(1) as p) as t;
>
> Here `update_get' allocates space in a memory context like
> `CurrentTransactionContext' or what ever is necessary in your case,
> puts the resulting typle into the allocated space and returns a
> pointer p to that tuple. `get_*' are accessor methods for such a
> pointer, accessing the allocated memory and retrieving the values
> needed.

    Hmm... That looks similar to what Tom had suggested might
eventually be supported. Would be a nice, clean, simple solution that
falls in line with standard SQL syntax.

> This all obviously requires some C-coding. But it works and would
> solve your problem.

    Ahh... That is how one does it now, vs. waiting until some one
implements a higher level version to be used from plpgsql. My need to
return is rows from a function (non-trigger) is not worth writing C
functions, at least not yet. Thanks for the help anyway, I will use your
suggestion as a starting point should I need to go that way. TTYL.

---------------------------------------------------------------------------
|   "For to me to live is Christ, and to die is gain."                    |
|                                            --- Philippians 1:21 (KJV)   |
---------------------------------------------------------------------------
|   Ryan Kirkpatrick  |  Boulder, Colorado  |  http://www.rkirkpat.net/   |
---------------------------------------------------------------------------