Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
That looks awfully suspect, and may be worth raising on pgsql-bugs. It looks like plan invalidation isn't being properly performed in multi-statement SQL functions. At the very least, that should cause an ERROR if it's not supported, or should replan the second statement. –  Craig Ringer Mar 15 at 10:01
    
Thanks for the suggestion Craig, I've raised it on pgsql-bugs (here). –  Jack Douglas Mar 16 at 15:28
add comment

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
I remembered something along these lines, just could not find the source (maybe should have tried it harder :) –  dezso Mar 17 at 9:01
    
@dezso: It really should be in the manual. The suggested fix should make that easier ... –  Erwin Brandstetter Mar 17 at 17:49
add comment

I ran some tests and saw the same. A temp table created inside an SQL function seems to be ignored. I agree with Craig, looks like a bug ...

However, the trick I advertised under the related question on SO last year still seems to work, but not for temp tables created inside an SQL function. In the the following demo an INSERT inside an SQL function is re-planned alright ...

CREATE TEMP TABLE work_list(
    name text,
    level smallint
);                       -- faking the table to allow function creation

CREATE OR REPLACE FUNCTION somefunc() RETURNS void AS
$BODY$
INSERT INTO work_list (name, level)
VALUES ('someone', 25);
$BODY$
LANGUAGE sql VOLATILE;       -- would throw an exception without temp table

SELECT somefunc();           -- works as expected
SELECT * FROM work_list;     -- result ok


DROP TABLE work_list;        -- drop the temp table

CREATE TEMP TABLE work_list( -- new temp table
    name text,
    level smallint
);

SELECT somefunc();           -- still works!
SELECT * FROM work_list;     -- result ok


DROP TABLE work_list;        -- drop the temp table
CREATE TABLE work_list(      -- create regular table
    name text,
    level smallint
);

SELECT somefunc();           -- works too (!)
SELECT * FROM work_list;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.