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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
