From: | "Kenneth Lundin" <kenneth(dot)lundin(at)dacom(dot)se> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | function to return both table row and varchar |
Date: | 2009-01-15 10:10:54 |
Message-ID: | 93d3a1c90901150210r16a496a3v8a4032f012b4f2ee@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
i'm defining a function in plpqsql and would like it to return one varchar
and one row from another table. I have defined it like this (this is only a
test and does not really make sense yet, but it's the principle i'm after):
CREATE OR REPLACE FUNCTION verify_record(IN number_to_verify bigint, OUT
resulting_row logbook, OUT result character varying)
RETURNS record AS
$BODY$
BEGIN
SELECT * INTO resulting_row FROM logbook WHERE
id_number=number_to_verify::varchar;
SELECT 'OK' INTO result;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100;
It works fine and i can do a select like this:
> select * from verify_record(1234);
resulting_row | result
------------------------------|--------
(1,"Test","Registered",.....) | "OK"
So far so good, but how do I use the the resulting_row further, say if i
would like to select only a few columns or perhaps define a view that
returns the 'result' column and only column 2 "Test" from the resulting_row?
What I'd like to do is a select and sub-address the individual columns of
the resulting_row, like writing (given 'name' is the name of some column in
resulting_row):
> select returned_row.name, result from verify_record(1234);
or perhaps
> select returned_row['name'], result from verify_record(1234);
and have it return something like:
name | result
-------|--------
"Test" | "OK"
Is this possible or am I on the wrong track here?
//Kenneth
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2009-01-15 10:14:10 | Re: fire trigger for a row without update? |
Previous Message | Christian Schröder | 2009-01-15 09:57:02 | Re: Polymorphic "setof record" function? |