2009/12/21 Wappler, Robert
<rwappler@ophardt.com>Hello,
when defining functions, why can’t they take table-valued arguments? I do not see the sense of this restriction. When a function takes a table as an argument, whole tables could be transformed in an obvious way. Currently functions must operate row-wise (at least I haven’t found another way). This also limits the capabilities of joining the row-arguments, obviously this must be done before passing the arguments to the functions, instead of having one or more table-arguments and simply doing the joins within the function. Further, table valued arguments could lower the developer’s work to prepare the arguments before the function call and enabling each functions to care for them itself by transforming them for the implementation instead of introducing several more or less anonymous record types just for passing values from one function to another which often also carry implementation specific constraints. The structure of the code as a set of functions would also greatly improve.
SQL itself is a language which operates on data sets. Interesting, why you need a functional interface here?
Could you give some example - what you want to achieve, and what's the missing element?
( please add some DDL and code if possible ).
As you know, every table in postgres has its rowtype definition.
CREATE TABLE aaa ( ... );
CREATE FUNCTION aaa_do (aaa) ...;
SELECT aaa_do(aaa.*) from aaa;
but that's the row-wise way.
You can also operate on tables via their *names*:
SELECT somefunc( 'aaa' ); /* some dynamic SQL inside */
You will find some examples in contrib/tablefunc
What does the optimizer do with this per-row-semantic, even if it is invoked on each row? Does it transform the queries within the function to directly work on the table and realize possible advantages from indexes and other techniques?
Yes that's what it does, in general.
But, if processing is all "canned" inside user defined functions, the optimizer can't do it's job.
some example from you would be handy here too.
regards,
--
Filip Rembiałkowski
JID,mailto:
filip.rembialkowski@gmail.comhttp://filip.rembialkowski.net/
