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


Thu, 18 Oct 2001 19:41:45 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Create or replace function doesn't work so well
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

Thu, 18 Oct 2001 16:15:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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


Fri, 19 Oct 2001 08:00:29 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Create or replace function doesn't work so well

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

Tue, 6 Nov 2001 17:41:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Create or replace function doesn't work so well
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Has this been resolved?

Yes.

			regards, tom lane

Tue, 06 Nov 2001 17:59:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Create or replace function doesn't work so well 