MIME-Version: 1.0
References: <153999002350.30914.3114277646917994661@wrigleys.postgresql.org>
 <22268.1539995600@sss.pgh.pa.us>
In-Reply-To: <22268.1539995600@sss.pgh.pa.us>
From: Pablo Benito <bioingbenito@gmail.com>
Date: Sat, 20 Oct 2018 21:37:56 -0300
Message-ID: 
 <CAME+NGWX2oozOrzuK=UVp_j185heLHzhJ8znjiQ8QBfsXRRsBQ@mail.gmail.com>
Subject: Re: The DROP TABLE instruction should have a TEMP option for when a
 temporary table
To: tgl@sss.pgh.pa.us
Cc: pgsql-docs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b709860578b259a0"
Precedence: bulk

--000000000000b709860578b259a0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =3D
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=C3=B3:

> =3D?utf-8?q?PG_Doc_comments_form?=3D <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
>

--000000000000b709860578b259a0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Tom,<div><br></div><div>Thanks a lot, for the response!=
<br></div><div><br></div><div>Yes, you are right, there is a secure way for=
 drops, prefixing with the schema name.</div><div>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.</div><div>(It is just an opini=
on)</div><div><br></div><div>Today I realized that,</div><div>and I&#39;ve =
created my functions:</div><div><br></div><div>--/*<br></div><div><div>;CRE=
ATE FUNCTION &quot;dailyDataProcessingEphemerals&quot;.&quot;getTemporalSch=
emaName&quot;()</div><div>RETURNS text</div><div>AS $$</div><div>SELECT quo=
te_ident(nspname) FROM pg_namespace WHERE=C2=A0 oid =3D pg_my_temp_schema()=
;</div><div>$$ LANGUAGE SQL;</div><div><br></div><div>-- RUN AS: ;SELECT * =
FROM &quot;dailyDataProcessingEphemerals&quot;.&quot;getTemporalSchemaName&=
quot;()</div><div><br></div><div>;CREATE FUNCTION &quot;dailyDataProcessing=
Ephemerals&quot;.&quot;dropTemporalTableIfExists&quot;(&quot;tableName&quot=
; text)</div><div>RETURNS void</div><div>LANGUAGE &quot;plpgsql&quot;</div>=
<div>AS $$</div><div>BEGIN</div><div>EXECUTE &#39;DROP TABLE IF EXISTS &#39=
; || (SELECT &quot;getTemporalSchemaName&quot; FROM &quot;dailyDataProcessi=
ngEphemerals&quot;.&quot;getTemporalSchemaName&quot;()) || &#39;.&#39; || q=
uote_ident(&quot;tableName&quot;);</div><div>END $$</div><div>--*/</div></d=
iv><div><br></div><div><br></div><div>Regards Pablo</div></div><br><div cla=
ss=3D"gmail_quote"><div dir=3D"ltr">El vie., 19 de oct. de 2018 a la(s) 21:=
33, Tom Lane (<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>) e=
scribi=C3=B3:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 =
0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">=3D?utf-8?q?PG_Doc_co=
mments_form?=3D &lt;<a href=3D"mailto:noreply@postgresql.org" target=3D"_bl=
ank">noreply@postgresql.org</a>&gt; writes:<br>
&gt; The issue is related to the intention of drop the temporary table:<br>
&gt; ;DROP TABLE &quot;myTooImportantTable&quot;=C2=A0 =C2=A0-- &lt;--- thi=
s drop the <br>
&gt; &quot;myTooImportantTable&quot; <br>
&gt; ;DROP TABLE &quot;myTooImportantTable&quot; -- &lt;--- this drop the<b=
r>
&gt; public.&quot;myTooImportantTable&quot;<br>
<br>
If you want to be sure you drop a temp table and not a regular one, say<br>
<br>
DROP TABLE pg_temp.mytable;<br>
<br>
There&#39;s no need for new syntax.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--000000000000b709860578b259a0--