Re: Create or replace function doesn't work so well

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