Re: Re: BUG #9578: Undocumented behaviour for temp tables created inside query language (SQL) functions - Mailing list pgsql-bugs

On Mon, Mar 17, 2014 at 4:15 PM, David Johnston <polobo@yahoo.com> wrote:
> 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@.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.

How about attached documentation patch as per the discussion?

Regards,
Hari Babu
Fujitsu Australia