Alter Temporary table in Stored Procedure

"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

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

"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