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

On Mon, Mar 17, 2014 at 2:42 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> Haribabu Kommi-2 wrote
>> On Sun, Mar 16, 2014 at 3:50 AM, <
>
>> jack(at)(dot)co
>
>> > 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.
>
>
>
> This seems to be the case; a DML statement inside an SQL function cannot
> access any temporary tables created within the same function.
>
> Based on this I have two documentation suggestions:
>
> Add a paragraph/sentence to: CREATE TABLE -> TEMPORARY or TEMP
> """
> Note that due to the nature of SQL-language function parsing (see section
> 35.4) it is not possible to both define and use a temporary table in the
> same function.
> """
>
> Add to 35.4 - Query Language (SQL) Functions
> 35.4.0 - Parsing Mechanics
> (this seems important enough for a sub-section and not just a paragraph in
> the introduction)
> """
> The body of an SQL function is parsed as if it were a single - multi-part -
> statement and thus uses a constant snapshot of the system catalog for every
> sub-statement therein. Commands that alter the catalog will likely not work
> as expected.
>
> For example: Issuing "CREATE TEMP TABLE" within an SQL function will add the
> table to the catalog but subsequent statements in the same function will not
> see those additions and thus the temporary table will be invisible to them.
>
> Thus it is generally advised that pl/pgsql be used, instead of SQL, when
> non-SELECT/INSERT/UPDATE/DELETE statements are required.
> """

Thanks. The proposed documentation changes are good for the user to
understand the behavior.

Regards,
Hari Babu
Fujitsu Australia