| 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: | Whole Thread | Raw Message | 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 |