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 | 3
5 | 9 | 1
4 | 0 | 0
2 | 0 | 0
0 | 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 | 3
5 | 9 | 1
4 | 0 | 0
2 | 0 | 0
0 | 0 | 0
(5 rows)

Maybe somebody else can suggest improvements or alternatives.

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