From: Michael Fuhr <mike@fuhr.org>
To: Federico Pedemonte <fepede@fastwebnet.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: Multiple SRF parameters from query
Date: Sat, 4 Jun 2005 18:52:24 -0600
Message-ID: <20050605005224.GA66396@winnie.fuhr.org> (raw)
In-Reply-To: <1117901975.4210.9.camel@localhost.localdomain>
References: <02767D4600E59A4487233B23AEF5C59922C2B7@blrmail1.aus.pervasive.com>
<1117901975.4210.9.camel@localhost.localdomain>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, fepede@fastwebnet.it
Subject: Re: Multiple SRF parameters from query
In-Reply-To: <20050605005224.GA66396@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox