How does one return rows from plpgsql functions?

From: Ryan Kirkpatrick <pgsql(at)rkirkpat(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: How does one return rows from plpgsql functions?
Date: 2002-01-19 18:28:45
Message-ID: Pine.LNX.4.21.0201191119130.24200-100000@magellan.rkirkpat.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2002-01-19 18:46:50 Re: large file limitation
Previous Message Frank Bax 2002-01-19 17:42:34 Re: Long running queries and timeouts