Re: How does one return rows from plpgsql functions?

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/ |
---------------------------------------------------------------------------