Thread: trying to write a many-to-many type function.
Hi,
I'd like to write a PostgreSQL server function (ideally in C) that takes a set of objects and returns 1 or more rows from each object, such that the rows returned can be cast to a desired rowtype. The structure of the returned rows depends on additional arguments. For example, I'd like to be able to write a function like the 'getMetaData' function shown below:
psql>select oid, getMetaData(myobject, "x,y,z")::t(x float, y float, c float) from mytable;
93939 | (3,4,5)
93939 | (3,4,9)
93939 | (3,4,2)
93939 | (4,3,1)
93940 | (4,3,1)
93940 | (3,1,4)
What I've tried:
By following the examples in the contrib directory, I managed to write a function that returned "setof record", but I can only cast its results if the function is in the from clause, ie:
select * from getMetaData('some text...'::myobjecttype) as t(x float, y float, c float);
Which limits me to calling the function with a single object.
As far as I can see, you can't write a set returning function that takes a set as input, as in
select getMetaData( select * from mytable);
Is a function of the type I'm trying to write even possible in PostgreSQL?
I'd like to write a PostgreSQL server function (ideally in C) that takes a set of objects and returns 1 or more rows from each object, such that the rows returned can be cast to a desired rowtype. The structure of the returned rows depends on additional arguments. For example, I'd like to be able to write a function like the 'getMetaData' function shown below:
psql>select oid, getMetaData(myobject, "x,y,z")::t(x float, y float, c float) from mytable;
93939 | (3,4,5)
93939 | (3,4,9)
93939 | (3,4,2)
93939 | (4,3,1)
93940 | (4,3,1)
93940 | (3,1,4)
What I've tried:
By following the examples in the contrib directory, I managed to write a function that returned "setof record", but I can only cast its results if the function is in the from clause, ie:
select * from getMetaData('some text...'::myobjecttype) as t(x float, y float, c float);
Which limits me to calling the function with a single object.
As far as I can see, you can't write a set returning function that takes a set as input, as in
select getMetaData( select * from mytable);
Is a function of the type I'm trying to write even possible in PostgreSQL?
**********************************************
Eric Davies, M.Sc.
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: http://www.barrodale.com
Email: eric@barrodale.com
**********************************************
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
**********************************************
Eric Davies <Eric@barrodale.com> writes:
> As far as I can see, you can't write a set returning function that
> takes a set as input, as in
> select getMetaData( select * from mytable);
> Is a function of the type I'm trying to write even possible in PostgreSQL?
I don't think it's possible at the moment :-(. We've speculated about
ways to relax the restriction that a function in FROM can't take any
non-constant arguments. It appears that adding SQL99's LATERAL and/or
UNNEST features might do it ... I've had a hard time finding any very
clear explanation of their semantics, though, so I'm not entirely sure.
regards, tom lane