Обсуждение: function accepting and returning rows; how to avoid parentheses
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
On 12/13/06, Kevin Murphy <murphy@genome.chop.edu> wrote: > 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. > > -- 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) functions defined in the sql language (as opposed to pl/pgsql) allow you to call them without explicitly using from...if you want to expand, just select from your result as you would expand any row variable. basically, have you tried: select (explode(sometable.*)).* from sometable; merlin
Merlin Moncure wrote: > On 12/13/06, Kevin Murphy <murphy@genome.chop.edu> wrote: >> 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. > > functions defined in the sql language (as opposed to pl/pgsql) allow > you to call them without explicitly using from...if you want to > expand, just select from your result as you would expand any row > variable. basically, have you tried: > > select (explode(sometable.*)).* from sometable; > Thanks a lot. You must have been puzzled that I got so close and failed to figure it out. I hadn't really understood the {row}.* notation. In the meantime I did an end run with an insert trigger, but at least I understand this now. -Kevin