From: | Kevin Murphy <murphy(at)genome(dot)chop(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | function accepting and returning rows; how to avoid parentheses |
Date: | 2006-12-13 00:42:57 |
Message-ID: | 457F4C91.7050702@genome.chop.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'd like to have a function that acts as a row filter (that can
optionally expand each row into multiple rows), but I don't know how to
wangle this such that the output is not enclosed in parentheses, i.e.
what I'm getting now is a single column of a composite type instead of
multiple columns matching the original table layout.
Example:
CREATE TABLE sometable (key text, value real);
INSERT INTO sometable VALUES ('A', 1);
INSERT INTO sometable VALUES ('B', 2);
-- unrealistic demo filter function
CREATE OR REPLACE FUNCTION foo(arow sometable) RETURNS SETOF sometable AS $$
DECLARE
BEGIN
RETURN NEXT arow;
RETURN NEXT arow;
RETURN;
END;
$$ LANGUAGE plpgsql;
-- SQL front-end for filter function
CREATE OR REPLACE FUNCTION explode(sometable) RETURNS SETOF sometable as $$
SELECT * FROM foo($1) AS t;
$$ LANGUAGE SQL STRICT IMMUTABLE;
select explode(sometable.*) from sometable;
explode
-----------
(A,1)
(A,1)
(B,2)
(B,2)
Thanks,
Kevin
From | Date | Subject | |
---|---|---|---|
Next Message | Brendan O'Shea | 2006-12-13 03:14:40 | Re: Statement timeout not working on broken connections with active queries |
Previous Message | Roderick A. Anderson | 2006-12-13 00:41:59 | changing the permission of _lots_ of tables |