Create or replace function doesn't work so well
peter=# drop function test();
DROP
peter=# create or replace function test() returns int as 'return 1;' language plperl;
CREATE
peter=# select test();
test
------
1
(1 row)
peter=# create or replace function test() returns int as 'return 2;' language plperl;
CREATE
peter=# select test();
test
------
1
(1 row)
The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
and plain SQL. Obviously, there is some caching going on, and a session
restart fixes everything, but the failure with this plain and simple test
case makes me wonder about this new feature...
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut <peter_e@gmx.net> writes:
The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
and plain SQL. Obviously, there is some caching going on, and a session
restart fixes everything, but the failure with this plain and simple test
case makes me wonder about this new feature...
Hmm. I fixed plplgsql a few days ago, but I was unaware that the other
PLs cached anything. Will look.
regards, tom lane
Peter,
On Thu, 18 Oct 2001, Peter Eisentraut wrote:
peter=# drop function test();
DROP
[snip]
The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
and plain SQL. Obviously, there is some caching going on, and a session
restart fixes everything, but the failure with this plain and simple test
case makes me wonder about this new feature...
I cannot recreate this on my devel system with plain SQL
template1=# drop function test();
DROP
template1=# create or replace function test() returns int as 'select 1;'
language 'sql';
CREATE
template1=# select test();
test
------
1
(1 row)
template1=# create or replace function test() returns int as 'select 2;'
language 'sql';
CREATE
template1=# select test();
test
------
2
(1 row)
However,
template1=# create or replace function test() returns int as 'begin
template1'# return ''1'';
template1'# end;
template1'# ' language 'plpgsql';
CREATE
template1=# select test();
test
------
1
(1 row)
template1=# create or replace function test() returns int as 'begin
template1'# return ''2'';
template1'# end;
template1'# ' language 'plpgsql';
CREATE
template1=# select test();
test
------
1
(1 row)
Yet,
template1=# create or replace function test() returns int as 'select 3'
language 'sql';
CREATE
template1=# select test();
test
------
3
(1 row)
So, it must be caching at of procedural (C??) functions. Apologies for not
testing this on all languages -- I presumed what was good for SQL would be
good for PLpgSQL ;).
I'll look into further but.
Gavin
Has this been resolved?
---------------------------------------------------------------------------
Peter,
On Thu, 18 Oct 2001, Peter Eisentraut wrote:
peter=# drop function test();
DROP[snip]
The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
and plain SQL. Obviously, there is some caching going on, and a session
restart fixes everything, but the failure with this plain and simple test
case makes me wonder about this new feature...I cannot recreate this on my devel system with plain SQL
template1=# drop function test();
DROP
template1=# create or replace function test() returns int as 'select 1;'
language 'sql';
CREATE
template1=# select test();
test
------
1
(1 row)template1=# create or replace function test() returns int as 'select 2;'
language 'sql';
CREATE
template1=# select test();
test
------
2
(1 row)However,
template1=# create or replace function test() returns int as 'begin
template1'# return ''1'';
template1'# end;
template1'# ' language 'plpgsql';
CREATE
template1=# select test();
test
------
1
(1 row)template1=# create or replace function test() returns int as 'begin
template1'# return ''2'';
template1'# end;
template1'# ' language 'plpgsql';
CREATE
template1=# select test();
test
------
1
(1 row)Yet,
template1=# create or replace function test() returns int as 'select 3'
language 'sql';
CREATE
template1=# select test();
test
------
3
(1 row)So, it must be caching at of procedural (C??) functions. Apologies for not
testing this on all languages -- I presumed what was good for SQL would be
good for PLpgSQL ;).I'll look into further but.
Gavin
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026