Date: Sat, 4 Jun 2005 18:52:24 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Federico Pedemonte <fepede@fastwebnet.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: Multiple SRF parameters from query
Message-ID: <20050605005224.GA66396@winnie.fuhr.org>
References: 
 <02767D4600E59A4487233B23AEF5C59922C2B7@blrmail1.aus.pervasive.com>
	<1117901975.4210.9.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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/