Skip site navigation (1) Skip section navigation (2)

Re: Passing arguments to views

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chris Campbell <chris(at)bignerdranch(dot)com>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, Tino Wildenhain <tino(at)wildenhain(dot)de>, Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Passing arguments to views
Date: 2006-02-03 17:27:26
Message-ID: 27386.1138987646@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Rick GiggerDate: 2006-02-03 17:43:38
Subject: Re: Multiple logical databases
Previous:From: Chris CampbellDate: 2006-02-03 16:38:14
Subject: Re: Passing arguments to views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group