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 ]
