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