Howto return values from a function - Mailing list pgsql-general

From A B
Subject Howto return values from a function
Date
Msg-id dbbf25900805150525m6dfbb798w653781c4c2ee8d8b@mail.gmail.com
Whole thread Raw
Responses Re: Howto return values from a function  (Martijn van Oosterhout <kleptog@svana.org>)
List pgsql-general
I'm still trying to learn to write plpgsql functions, but I find the
docs a little short on examples on how to return stuff from a
function. I'm very grateful for any help on this.

There are some basic cases I've identified.

1) when I want to return all records found by a query , like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
     RETURN QUERY SELECT a,b,c,d,... FROM T1,T2,... WHERE ....;
END;

but what do I write instead of ????

2) when I select stuff, iterate over the result before returning it
CREATE  FUNCTION foo() RETURNS ????  AS
BEGIN
    FOR result IN  SELECT  .....
    LOOP
          do something with result...
        RETURN NEXT result
    END LOOP;
END

I'm nto sure here, but It seems to mee that there are two other ways
of doing case 2.
2a) run the query once more and RETURN QUERY at the end instead of the
RETURN NEXT statement.
2b) store the result in some temporary storage... (I'm not sure how,
it's just a feeling I get that this should be possible, I might be
completely wrong) and then return the whole result a once.
As usual, what do I write instead of ????

3) In the third case, I want to create the values I return  by joining
many values. Something like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
    myvar := .....
    myvar2 := ....
    FOR result IN  SELECT ...
    LOOP
          FOR result2 IN SELECT .....
          LOOP
               RETURN NEXT  ????????????;
          END LOOP;
    END LOOP;
    RETURN
END

The ?????????????? part should perhaps be something like rows with the values
[ myvar,  myvar2,  result.f1,   result.f2,   result2.f5,   result2.f7  ]

pgsql-general by date:

Previous
From: "Pavan Deolasee"
Date:
Subject: Re: Need for help!
Next
From: John DeSoi
Date:
Subject: Re: problem with importing dbf file into postgresql using navicat on mac OS X