Re: how to return parts of records from a function

A B wrote:
> Hello.
> I think I need som help on this function I write in plpgsql
> I want to return
> CREATE OR REPLACE FUNCTION gList(tid_ TIMESTAMP) RETURNS AS $$
> DECLARE
> rec RECORD;
> BEGIN
> FOR rec IN SELECT DISTINCT custid,action,nr FROM ...
> IF rec.action = ...
> END IF;
> RETURN NEXT ???????????????
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> I want to replace the ????????? with something that will contain both
> rec.nr and rec.action. What should that looklike? rec.nr ,
> rec.action ?

Declare another RECORD for the return value, then assign to its fields
with the := operator. If you add the following in your DECLARE block:

retval RECORD;

then use:

retval.nr := rec.nr;
retval.action := rec.action;

you can RETURN NEXT retval.

If you have lots of values to return you can use a row constructor:

retval := row(rec.nr, rec.action)
return next retval;

It's also possible to do the same job using a function that has OUT
parameters, but the record approach should be fine for you.

> And what should I write after RETURNS .... in the first line? RECORD?

SETOF RECORD

> Oh, now that I write this I understand that I could replace the RETURN
> NEXT in the loop with a single RETURN QUERY at the end... I don't know
> if that will be good, but I still would like to know how to return the
> separate fields from the rec record.

Depending on the contents of your `IF rec.action' you might able to
rewrite the whole thing as single SQL statement (using CASE instead of
IF) and wrap it up in an SQL stored procedure.

Otherwise RETURN QUERY won't do you much good, because you need to step
through the results of the query and do something with them.

--
Craig Ringer