CODEWITHSUNDEEP
postgresqlscopetemp-tables
William Meitzen
William Meitzen

Reputation: 63

PostgreSQL (9.4) temporary table scope

After some aggravation, I found (IMO) odd behavior when a function calls another. If the outer function creates a temporary table, and the inner function creates a temporary table with the same name, the inner function "wins." Is this intended? FWIW, I am proficient at SQL Server, and temporary tables do not act this way. Temporary tables (#temp or @temp) are scoped to the function. So, an equivalent function (SQL Server stored procedure) would return "7890," not "1234."

drop function if exists inner_function();
drop function if exists outer_function();

create function inner_function()
returns integer
as
$$
begin
    drop table if exists tempTable;
    create temporary table tempTable (
        inner_id int
    );
    insert into tempTable (inner_id) values (1234);
    return 56;
end;
$$
language plpgsql;

create function outer_function()
returns table (
    return_id integer
)
as
$$
    declare intReturn integer;
begin
    drop table if exists tempTable; -- note that inner_function() also declares tempTable
    create temporary table tempTable (
         outer_id integer
    );
    insert into tempTable (outer_id) values (7890);
    intReturn = inner_function(); -- the inner_function() function recreates tempTable
    return query
        select * from tempTable; -- returns "1234", not "7890" like I expected
end;
$$
language plpgsql;

select * from outer_function(); -- returns "1234", not "7890" like I expected

Upvotes: 0

Views: 2390

Answers (2)

David Aldridge
David Aldridge

Reputation: 52376

Is this intended?

Yes, these are tables in the database, similar to permanent tables.

They exist in a special schema, and are automatically dropped at the end of a session or transaction. If you create a temporary table with the same name as a permanent table, then you must prefix the permanent table with its schema name to reference it while the temporary table exists.

If you want to emulate the SQL Server implementation then you might consider using particular prefixes for your temporary tables.

Upvotes: 0

fabriziomello
fabriziomello

Reputation: 66

There are no problem with this behaviour, in PostgreSQL temp table can have two scopes: - session (default) - transaction

To use the "transaction" scope you should use "ON COMMIT DROP" at the end of the CREATE TEMP statement, i.e:

CREATE TEMP TABLE foo(bar INT) ON COMMIT DROP;

Anyway your two functions will be executed in one transaction so when you call the inner_function from the outer_function you'll be in the same transaction and PostgreSQL will detect that "tempTable" already exists in the current session and will drop it in "inner_function" and create again...

Upvotes: 2

Related Questions