Re: BUG #9578: Undocumented behaviour for temp tables created inside query language (SQL) functions

On Sun, Mar 16, 2014 at 3:50 AM,  <jack@douglastechnology.co.uk> wrote:
> A temp table created inside an SQL function does not override existing
> permanent tables with the same name as the documentation here indicates it
> should:
>
> http://www.postgresql.org/docs/9.3/static/sql-createtable.html#AEN72676
>
> I've reproduced this on the major versions back to 8.4.
>
> More details, test case and investigation here:
>
> http://dba.stackexchange.com/q/60997/1396

I checked the test case which you given in the above link.
As you are seeing the difference in behavior of accessing a temp table
inside an SQL function
and PLPGSQL function.

Table:

create table foo(id) as values (1);
select * from foo;

SQL-function:

create or replace function f() returns setof integer language sql as $$
  create temporary table foo(id) as values (2);
  select id from foo;
$$;
select * from f();

PLPGSQL function:

create or replace function f() returns setof integer language plpgsql as $$
begin
  create temporary table foo(id) as values (2);
  return query select id from foo;
end;
$$;
select * from f();

This is because while executing the SQL function the entire function
body is parsed and executed.
But with the PLPGSQL function statement by statement is parsed and
executed. Because of this
reason the SQL function not able to see the temp table which is
created during the function execution.
That is the reason the result is different.

I don't think it is a bug.

Regards,
Hari Babu
Fujitsu Australia