Re: plan invalidation vs stored procedures

On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
> 2008/8/5 Asko Oja <ascoja(at)gmail(dot)com>:
> > 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.
> >>
>
> you cannot change header of function. It's same as change C header of
> function without complete recompilation.

SQL is not C.

You don't have to recompile the whole SQL database when you add columns
to tables, so why should you need to do it, when you add a column to
table-returning function ?

--------------
Hannu