Reputation: 37354
I have the following function in my postgresql database:
CREATE OR REPLACE FUNCTION get_unused_part_ids()
RETURNS integer[] AS
$BODY$
DECLARE
part_ids integer ARRAY;
BEGIN
create temporary table tmp_parts
as
select vendor_id, part_number, max(price) as max_price
from refinery_akouo_parts
where retired = false
group by vendor_id, part_number
having min(price) < max(price);
-- do some work etc etc
-- simulate ids being returned
part_ids = '{1,2,3,4}';
return part_ids;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION get_unused_part_ids()
OWNER TO postgres;
This compiles but when I run:
select get_unused_part_ids();
the temporary table, tmp_parts
, still exists. I can do a select on it after. Forgive me as I'm used to a particular functionality with t-sql/MSSQL. This wouldn't be the case with MSSQL. What am I doing wrong?
Upvotes: 1
Views: 6802
Reputation: 3318
After manual
Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below)
Session ends after disconnection. Not after transaction commit. So default behavior is to preserve temp table till your connection is still open. You must add ON COMMIT DROP;
to achive your desired behaviour:
create temporary table tmp_parts on commit drop
as
select vendor_id, part_number, max(price) as max_price
from refinery_akouo_parts
where retired = false
group by vendor_id, part_number
having min(price) < max(price)
on commit drop;
Upvotes: 3
Reputation: 292
The table will only be deleted at the end of the session. You need to specify the ON COMMIT option to drop, and it will drop the table at the end of the transaction.
create temporary table tmp_parts
on commit drop
as
select vendor_id, part_number, max(price) as max_price
from refinery_akouo_parts
where retired = false
group by vendor_id, part_number
having min(price) < max(price);
Upvotes: 6
Reputation: 1271241
Temporary tables are treated differently in the two databases. In SQL Server, they will be removed at the end of a stored procedure where they are created, automatically.
In Postgres, temporary tables are assigned to a session (or a transaction), as explained in the documentation:
If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). 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. Any indexes created on a temporary table are automatically temporary as well.
The concept is sort of between a regular temporary table and a global temporary table in SQL Server (global temporary tables start with ##
).
Upvotes: 2