Re: plan invalidation vs stored procedures

postgres=# create or replace function pavel ( i_param text, status OUT int,
status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
language sql;
CREATE FUNCTION
postgres=# create or replace function pavel ( i_param text, status OUT int,
status_text OUT text, more_text OUT text ) returns record as $$ select
200::int, 'ok'::text, 'tom'::text; $$ language sql;
ERROR: cannot change return type of existing function
DETAIL: Row type defined by OUT parameters is different.
HINT: Use DROP FUNCTION first.

On Tue, Aug 5, 2008 at 5:04 PM, Asko Oja <ascoja(at)gmail(dot)com> wrote:

> > This is simply a bad, wrong, stupid way to do it. Why do you not use
> > CREATE OR REPLACE FUNCTION?
> I totally agree we should get this fixed first :)
>
> postgres=# create or replace function pavel ( i_param text, status OUT int,
> status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
> language sql;
> ERROR: cannot change return type of existing function
> HINT: Use DROP FUNCTION first.
>
> On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com> writes:
>> > create function foo() returns integer as $$ begin return 1; end; $$
>> language plpgsql;
>> > CREATE FUNCTION
>> > prepare c1 as select * from foo();
>> > PREPARE
>> > execute c1;
>> > foo
>> > -----
>> > 1
>> > (1 row)
>>
>> > drop function foo();
>> > DROP FUNCTION
>> > create function foo() returns integer as $$ begin return 2; end; $$
>> language plpgsql;
>> > CREATE FUNCTION
>> > execute c1;
>> > psql:test.sql:11: ERROR: cache lookup failed for function 36555
>>
>> This is simply a bad, wrong, stupid way to do it. Why do you not use
>> CREATE OR REPLACE FUNCTION?
>>
>> regards, tom lane
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>