Is this temp table behaviour documented?

Query Language (SQL) and PL/pgSQL functions treat temp tables differently:

begin;
create table foo(id) as values (1);
select * from foo;
/*
 id
----
  1
*/
savepoint s;
create function f() returns setof integer language sql as $
  create temporary table foo(id) on commit drop as values (2);
  select id from foo;
$;
select * from f();
/*
 f
---
 1
*/
rollback to s;
create function f() returns setof integer language plpgsql as $
begin
  create temporary table foo(id) on commit drop as values (2);
  return query select id from foo;
end;
$;
select * from f();
/*
 f
---
 2
*/
rollback;

The docs say:


...Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names...

This does not mention the exception demonstrated above. Is this documented elsewhere?

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.

In other words this applies to other objects, not just temp tables. This is easily demonstrated too:

begin;
--
create function pg_temp.f() returns integer language sql as $select 1;$;
create function g() returns integer language sql as $g$
  create or replace function pg_temp.f() returns integer language sql as $select 2;$;
  select pg_temp.f();
$g$;
--
select g();
/*
 g
---
 1
*/
select g();
/*
 g
---
 2
 */
--
rollback;

or even:

begin;
create schema stack;
set search_path to stack;
create temp table foo(id) on commit drop as values (1);
select * from foo;
/*
 id
----
  1
*/
savepoint s;
create function f() returns setof integer language sql set search_path = stack, pg_temp as $
  create table foo(id) as values (2);
  select id from foo;
$;
select * from f();
/*
 f
---
 1
*/
rollback to s;
create function f() returns setof integer language plpgsql set search_path = stack, pg_temp as $
begin
  create table foo(id) as values (2);
  return query select id from foo;
end;
$;
select * from f();
/*
 f
---
 2
*/
rollback;

The suggestion on pgsql-bugs is that the documentation is amended to make this behaviour explicit.

edit:

A change to the documentation has apparently been comitted but isn't yet showing in the 9.4 docs

StackExchange Database Administrators Q#60997, answer score: 4