Postgres Pro
Downloads
Home   >   mailing lists

Thread: Alter Temporary table in Stored Procedure

Alter Temporary table in Stored Procedure

From
"Nykolyn, Andrew"
Date:

Does any know of a way that I can alter a temporary table by adding a serial column within a stored procedure after it has been created?  Any help greatly appreciated.  Thanks.

Andy Nykolyn
Northrop Grumman

Re: Alter Temporary table in Stored Procedure

From
Tom Lane
Date:
"Nykolyn, Andrew" <andrew.nykolyn@ngc.com> writes:
> Does any know of a way that I can alter a temporary table by adding a
> serial column within a stored procedure after it has been created?

It should just work.  What did you try, exactly, and what error message
did you get?

            regards, tom lane

Re: Alter Temporary table in Stored Procedure

From
"Nykolyn, Andrew"
Date:
> Does any know of a way that I can alter a temporary table by adding a
> serial column within a stored procedure after it has been created?

It should just work.  What did you try, exactly, and what error message
did you get?


I have the following code in my stored procedure:

    create temporary table t_resultset as select * from
get_createtempmsg();

    alter table t_resultset add column seq serial;

Where get_createtempmsg() is a stored procedure that returns a composite
type with no records.

The error I get is - relation "public.t_resultset"  does not exist.

I am using 8.1.3

Re: Alter Temporary table in Stored Procedure

From
Tom Lane
Date:
"Nykolyn, Andrew" <andrew.nykolyn@ngc.com> writes:
> I have the following code in my stored procedure:

>     create temporary table t_resultset as select * from
> get_createtempmsg();
>     alter table t_resultset add column seq serial;

> The error I get is - relation "public.t_resultset"  does not exist.

> I am using 8.1.3

Hmm, well, the case seems to work for me in 8.1.11, but only for rather
small values of "work": the sequence is created in the public schema,
which is the wrong place for a temp object.  On looking back, this whole
area is badly broken in releases before 8.3 --- see discussion of
bug #3403:
http://archives.postgresql.org/pgsql-bugs/2007-06/msg00114.php

We concluded that the fix was too invasive to risk back-patching,
so it's unlikely that anything will ever get done about it in 8.1.x
or 8.2.x.  If you really need this to work, I'd suggest updating
to 8.3.

            regards, tom lane