Thread: The DROP TABLE instruction should have a TEMP option for when atemporary table
The DROP TABLE instruction should have a TEMP option for when atemporary table
From
PG Doc comments form
Date:
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.3/static/sql-createtable.html Description: Hi, There is an obvious "issue" with temporary tables. Case description: We have a public table public."myTooImportantTable" Then you have a temporary table: ;CREATE TEMP TABLE "myTooImportantTable" The issue is related to the intention of drop the temporary table: ;DROP TABLE "myTooImportantTable" -- <--- this drop the "myTooImportantTable" ;DROP TABLE "myTooImportantTable" -- <--- this drop the public."myTooImportantTable"
Re: The DROP TABLE instruction should have a TEMP option for when a temporary table
From
Tom Lane
Date:
=?utf-8?q?PG_Doc_comments_form?= <noreply@postgresql.org> writes:
> The issue is related to the intention of drop the temporary table:
> ;DROP TABLE "myTooImportantTable" -- <--- this drop the
> "myTooImportantTable"
> ;DROP TABLE "myTooImportantTable" -- <--- this drop the
> public."myTooImportantTable"
If you want to be sure you drop a temp table and not a regular one, say
DROP TABLE pg_temp.mytable;
There's no need for new syntax.
regards, tom lane
Re: The DROP TABLE instruction should have a TEMP option for when atemporary table
From
Pablo Benito
Date:
Hi Tom,
Thanks a lot, for the response!
Yes, you are right, there is a secure way for drops, prefixing with the schema name.
But, for this particular case, TEMPORARY tables, as a special way for CREATE TABLE, It would be good to have also an special way for DROP TABLE.
(It is just an opinion)
Today I realized that,
and I've created my functions:
--/*
;CREATE FUNCTION "dailyDataProcessingEphemerals"."getTemporalSchemaName"()
RETURNS text
AS $$
SELECT quote_ident(nspname) FROM pg_namespace WHERE oid = pg_my_temp_schema();
$$ LANGUAGE SQL;
-- RUN AS: ;SELECT * FROM "dailyDataProcessingEphemerals"."getTemporalSchemaName"()
;CREATE FUNCTION "dailyDataProcessingEphemerals"."dropTemporalTableIfExists"("tableName" text)
RETURNS void
LANGUAGE "plpgsql"
AS $$
BEGIN
EXECUTE 'DROP TABLE IF EXISTS ' || (SELECT "getTemporalSchemaName" FROM "dailyDataProcessingEphemerals"."getTemporalSchemaName"()) || '.' || quote_ident("tableName");
END $$
--*/
Regards Pablo
El vie., 19 de oct. de 2018 a la(s) 21:33, Tom Lane (tgl@sss.pgh.pa.us) escribió:
PG Doc comments form <noreply@postgresql.org> writes:
> The issue is related to the intention of drop the temporary table:
> ;DROP TABLE "myTooImportantTable" -- <--- this drop the
> "myTooImportantTable"
> ;DROP TABLE "myTooImportantTable" -- <--- this drop the
> public."myTooImportantTable"
If you want to be sure you drop a temp table and not a regular one, say
DROP TABLE pg_temp.mytable;
There's no need for new syntax.
regards, tom lane