Re: Passing arguments to views

On Feb 3, 2006, at 12:27, Tom Lane wrote:

>> 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.

Consider function foo() that references table bar. When you CREATE  
FUNCTION foo() ... AS 'SELECT ... FROM bar' you get an error message  
if bar doesn't exist. If it does exist, CREATE FUNCTION succeeds.

If you later DROP bar, you're not informed that function foo() was  
referencing it. You only find that out if you redefine foo() (using  
CREATE OR REPLACE FUNCTION and passing in the same definition, which  
fails) or if you try to run foo() (and the query fails).

If functions had true dependency tracking, then you couldn't DROP bar  
due to foo()'s dependency on it, unless you did a DROP CASCADE and  
were alerted that foo() was dropped as well.

I'm fine with those limitations. I can confirm that all of my  
functions are not referencing tables that don't exist by doing a  
CREATE OR REPLACE FUNCTION to reload each function. A pg_dump/ 
pg_restore would accomplish this, but it would be nice to have a  
"RELOAD FUNCTION" (or "REPARSE"? or "VERIFY"?) command that would  
just re-parse the function's source code (like CREATE FUNCTION does)  
and spit out errors if the function is referencing relations that  
don't exist. Just as a way to confirm that the table modification I  
just performed didn't break any functions. On-demand dependency  
checking, in a way.

> 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=#

Learn something new every day. I'm still using 7.4 for most of my day  
job, and I can't do this without supplying a column definition list:

ERROR:  a column definition list is required for functions returning  
"record"

I hereby withdraw my proposal for "CREATE SQL FUNCTION."

Thanks!

- Chris