Re: Multiple SRF parameters from query
От | Michael Fuhr |
---|---|
Тема | Re: Multiple SRF parameters from query |
Дата | |
Msg-id | 20050605005224.GA66396@winnie.fuhr.org обсуждение исходный текст |
Ответ на | Re: Multiple SRF parameters from query (Federico Pedemonte <fepede@fastwebnet.it>) |
Список | pgsql-sql |
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/
В списке pgsql-sql по дате отправления:
Предыдущее
От: Bruce MomjianДата:
Сообщение: Re: ARRAY() returning NULL instead of ARRAY[] resp. {}