Re: Multiple SRF parameters from query

On Sat, Jun 04, 2005 at 06:19:35PM +0200, Federico Pedemonte wrote:
> 
> What i'm trying to do is a query where i get a result of this kind
> 
> a | b | c
> --+---+---
> 1 | 2 | 3     
> 5 | 9 | 1
> 4 | 0 | 0 
> 2 | 0 | 0
> 0 | 0 | 0 
> 
> given that i select from the table Anagrafica the fields 'aaa' and
> 'bbb', where the first 2 rows obtained as the result of foo ('aaa') and
> the latter 3 as the result of foo ('bbb').

If you don't mind using a deprecated feature that might be removed
from future versions of PostgreSQL, then see "SQL Functions Returning
Sets" in the documentation:

http://www.postgresql.org/docs/8.0/static/xfunc-sql.html#AEN29555

To use the deprecated feature, you could wrap a complex PL/pgSQL
SRF inside a simple SQL SRF.  The following example works for me
in 8.0.3:

CREATE TABLE anagrafica (   id  text PRIMARY KEY,   n   integer NOT NULL
);

INSERT INTO anagrafica (id, n) VALUES ('aaa', 1);
INSERT INTO anagrafica (id, n) VALUES ('bbb', 5);
INSERT INTO anagrafica (id, n) VALUES ('ccc', 9);
INSERT INTO anagrafica (id, n) VALUES ('ddd', 10);
INSERT INTO anagrafica (id, n) VALUES ('eee', 11);

CREATE TYPE footype AS (   a  integer,   b  integer,   c  integer
);

CREATE FUNCTION foo(id text) RETURNS SETOF footype AS $$
DECLARE   rec  footype;
BEGIN   IF id = 'aaa' THEN       rec.a := 1; rec.b := 2; rec.c := 3; RETURN NEXT rec;       rec.a := 5; rec.b := 9;
rec.c:= 1; RETURN NEXT rec;       RETURN;   ELSIF id = 'bbb' THEN       rec.a := 4; rec.b := 0; rec.c := 0; RETURN NEXT
rec;      rec.a := 2; rec.b := 0; rec.c := 0; RETURN NEXT rec;       rec.a := 0; rec.b := 0; rec.c := 0; RETURN NEXT
rec;      RETURN;   ELSE       rec.a := -1; rec.b := -2; rec.c := -3; RETURN NEXT rec;       RETURN;   END IF;
 
END;
$$ LANGUAGE plpgsql STABLE STRICT;

CREATE FUNCTION bar(text) RETURNS SETOF footype AS $$
SELECT * FROM foo($1);
$$ LANGUAGE sql STABLE STRICT;

SELECT (bar(id)).* FROM anagrafica WHERE n <= 5;a | b | c 
---+---+---1 | 2 | 35 | 9 | 14 | 0 | 02 | 0 | 00 | 0 | 0
(5 rows)

I don't know if there's a way to do this in a simple query without
relying on the deprecated behavior.  For forward compatibility, you
might be better off writing a SRF that makes a query and loops
through the results, like this:

CREATE FUNCTION baz(query text) RETURNS SETOF footype AS $$
DECLARE   rec     record;   retval  footype;
BEGIN   FOR rec IN EXECUTE query LOOP       IF rec.id = 'aaa' THEN           retval.a := 1; retval.b := 2; retval.c :=
3;RETURN NEXT retval;           retval.a := 5; retval.b := 9; retval.c := 1; RETURN NEXT retval;       ELSIF rec.id =
'bbb'THEN           retval.a := 4; retval.b := 0; retval.c := 0; RETURN NEXT retval;           retval.a := 2; retval.b
:=0; retval.c := 0; RETURN NEXT retval;           retval.a := 0; retval.b := 0; retval.c := 0; RETURN NEXT retval;
ELSE           retval.a := -1; retval.b := -2; retval.c := -3; RETURN NEXT retval;       END IF;   END LOOP;
 
   RETURN;
END;  
$$ LANGUAGE plpgsql STABLE STRICT;

SELECT * FROM baz('SELECT * FROM anagrafica WHERE n <= 5');a | b | c 
---+---+---1 | 2 | 35 | 9 | 14 | 0 | 02 | 0 | 00 | 0 | 0
(5 rows)

Maybe somebody else can suggest improvements or alternatives.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/