Re: function to return both table row and varchar

Sorry, I should have RTFM(!!!). I found it under 4.2.4 Field selection.
Apparently it works just as I want, but I should have put parenthesis around
the row-name like this:

> select result,(resulting_row).name from verify_record(1234);
name | result
-------|--------
"Test" | "OK"

I also discovered you can do a
> select result,(resulting_row).* from verify_record(1234);
to combine the both results to a single returning row if needed... sweet!

//Kenneth

On Thu, Jan 15, 2009 at 11:10 AM, Kenneth Lundin <kenneth(dot)lundin(at)dacom(dot)se>wrote:

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