Re: Passing arguments to views

Chris Campbell <chris(at)bignerdranch(dot)com> writes:
> True, as long as there's a hook to do the inlining/rewriting before
> the query's planned. I guess we can see function calls at the parse
> stage, check to see if they're SQL functions or not, grab the prosrc,
> do the substitution, then re-parse?

pull_up_subqueries in prepjointree.c would be the appropriate place
I think: if it's an RTE_FUNCTION RTE, look to see if function is SQL
and has the other needed properties, if so replace it by an RTE_SUBQUERY
RTE with the correct subquery, then recurse to try to flatten the
subquery. (Note: I'm in the middle of hacking that code to flatten
UNION subqueries, so you might want to wait till I commit before
starting on a patch ;-))

> I guess I can live without the dependancy tracking. I can always dump
> and reload my database to re-parse all the functions. Maybe we could
> have a RELOAD FUNCTION command that would just re-parse an existing
> function, so I don't have to dump and reload?

Hm? I don't understand why you think this is needed.

> What about auto-creating a composite type for the function's return
> type based on the query definition?

Can't get excited about this --- although I don't have any fundamental
objection either. Choosing a name for such a type might be a bit of
a problem (I don't think you can just use the function name, as that
would preclude overloading).

> Maybe an extension to CREATE FUNCTION as a shorthand for set-
> returning SQL functions?

It would be surprising if this form of CREATE FUNCTION defaulted to
assuming SETOF when other forms don't, so I don't like the proposal
as written. Also the syntax you suggest has noplace to put function
attributes like VOLATILE.

Note that you can already do

regression=# create function fooey(int, out k1 int, out k2 int) returns setof record as
regression-# $$ select unique1, unique2 from tenk1 where thousand = $1 $$ language sql;
CREATE FUNCTION
regression=# select * from fooey(44);
k1 | k2
------+------
7044 | 562
5044 | 692
1044 | 789
4044 | 1875
3044 | 3649
2044 | 4063
8044 | 6124
6044 | 6451
9044 | 6503
44 | 7059
(10 rows)

regression=#

I'm not convinced that the incremental advantage of not having to write
out the function output column types is worth introducing an
inconsistent variant of CREATE FUNCTION. Some indeed would argue that
that's not an advantage at all ;-)

regards, tom lane