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

Haribabu Kommi-2 wrote
> On Mon, Mar 17, 2014 at 2:42 PM, David Johnston <

> polobo@

> > 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.

Although "CREATE TEMP TABLE" probably shouldn't be special-cased for this
since it is no more or less likely to be mis-used in this way than CREATE
TYPE or CREATE FUNCTION. The comment in the SQL language area should be
sufficient as creating such a function can be expected to have at least read
that section and to have seen the "parsing rules" note that pertains to all
of these.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9578-Undocumented-behaviour-for-temp-tables-created-inside-query-language-SQL-functions-tp5796176p5796272.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.