public inbox for [email protected]  

help / color / mirror / Atom feed

The DROP TABLE instruction should have a TEMP option for when a temporary table
3+ messages / 3 participants
[nested] [flat]

* The DROP TABLE instruction should have a TEMP option for when a temporary table
@ 2018-10-19 23:00  PG Doc comments form <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: PG Doc comments form @ 2018-10-19 23:00 UTC (permalink / raw)
  To: [email protected]; +Cc: [email protected]

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"


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: The DROP TABLE instruction should have a TEMP option for when a temporary table
@ 2018-10-20 00:33  Tom Lane <[email protected]>
  parent: PG Doc comments form <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2018-10-20 00:33 UTC (permalink / raw)
  To: [email protected]; +Cc: [email protected]

=?utf-8?q?PG_Doc_comments_form?= <[email protected]> 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: The DROP TABLE instruction should have a TEMP option for when a temporary table
@ 2018-10-21 00:37  Pablo Benito <[email protected]>
  parent: Tom Lane <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: Pablo Benito @ 2018-10-21 00:37 UTC (permalink / raw)
  To: [email protected]; +Cc: [email protected]

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 ([email protected])
escribió:

> =?utf-8?q?PG_Doc_comments_form?= <[email protected]> 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
>


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2018-10-21 00:37 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-19 23:00 The DROP TABLE instruction should have a TEMP option for when a temporary table PG Doc comments form <[email protected]>
2018-10-20 00:33 ` Tom Lane <[email protected]>
2018-10-21 00:37   ` Pablo Benito <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox