Re: plan invalidation vs stored procedures

On Tue, Aug 5, 2008 at 10:12 AM, Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com> wrote:
>>> 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?
>>
>
> Well, the test case was an illustration. The actual reason for DROP and CREATE is
> the inability to change function return type. In our case there are plpgsql OUT
> parameters involved, and there is no other way to add additional OUT parameters
> without dropping the function first. I'd be glad if this was fixed, but I still
> think that proper plan invalidation for function changes is needed (inlined
> functions, ALTER FUNCTION stuff etc.)

one workaround is to use a table based custom composite type:

create table foo_output(a int, b text);

create function foo() returns foo_output as ...

alter table foo_output add column c int;

create or replace foo() if necessary. This also works for 'in' variables.

voila! :-) note you can't use standard composite type because there
is no way to 'alter' it.

merlin