pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDROP TYPE without error?
5+ messages / 5 participants
[nested] [flat]
* DROP TYPE without error?
@ 2005-04-06 12:59 Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 3 replies; 5+ messages in thread
From: Philippe Lang @ 2005-04-06 12:59 UTC (permalink / raw)
To: pgsql-sql
Hi,
Since it is not possible to use CREATE OR REPLACE TYPE, is there a way
of using DROP TYPE on a non-existing type, without causing the entire
script to abort? It may sound crazy to ask for this, but it could be
really useful in my case, where Pl/Pgsql and Pl/Perl code is being
generated automatically, based on data found in a database.
Thanks
-----------------
Philippe Lang
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP TYPE without error?
@ 2005-04-06 20:03 Mischa <mischa.Sandberg@telus.net>
parent: Philippe Lang <philippe.lang@attiksystem.ch>
2 siblings, 1 reply; 5+ messages in thread
From: Mischa @ 2005-04-06 20:03 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql
Quoting Philippe Lang <philippe.lang@attiksystem.ch>:
> Since it is not possible to use CREATE OR REPLACE TYPE, is there a way
> of using DROP TYPE on a non-existing type, without causing the entire
> script to abort? It may sound crazy to ask for this, but it could be
> really useful in my case, where Pl/Pgsql and Pl/Perl code is being
> generated automatically, based on data found in a database.
I've got a similar request for other objects that do/do not exist.
Maybe it's just that I got lazy using MSSQL, but it sure was convenient
to have:
IF object_id('WorkTable') IS NULL
CREATE TABLE WorkTable(...
etc.
Given that you cannot just execute an anonymous block of PL/PGSQL code,
where you could do the test AND the create ...
--
"Dreams come true, not free."
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP TYPE without error?
@ 2005-04-07 12:37 Harald Fuchs <use_reply_to@protecting.net>
parent: Mischa <mischa.Sandberg@telus.net>
0 siblings, 0 replies; 5+ messages in thread
From: Harald Fuchs @ 2005-04-07 12:37 UTC (permalink / raw)
To: pgsql-sql
In article <1112817807.4254408fecadc@webmail.telus.net>,
Mischa <mischa.Sandberg@telus.net> writes:
> I've got a similar request for other objects that do/do not exist.
> Maybe it's just that I got lazy using MSSQL, but it sure was convenient
> to have:
> IF object_id('WorkTable') IS NULL
> CREATE TABLE WorkTable(...
> etc.
I got lazy using MySQL, where it was convenient to have
CREATE TABLE IF NOT EXISTS tbl (...)
and
DROP TABLE IF EXISTS tbl
This is the only feature of MySQL I really miss.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP TYPE without error?
@ 2005-04-07 19:48 Jeff Boes <jboes@nexcerpt.com>
parent: Philippe Lang <philippe.lang@attiksystem.ch>
2 siblings, 0 replies; 5+ messages in thread
From: Jeff Boes @ 2005-04-07 19:48 UTC (permalink / raw)
To: pgsql-sql
Philippe Lang wrote:
> Hi,
>
> Since it is not possible to use CREATE OR REPLACE TYPE, is there a way
> of using DROP TYPE on a non-existing type, without causing the entire
> script to abort? It may sound crazy to ask for this, but it could be
> really useful in my case, where Pl/Pgsql and Pl/Perl code is being
> generated automatically, based on data found in a database.
>
If I understand you correctly, then this might be useful:
begin;
select now();
\o tmp.tmp
\qecho 'drop type \"foofookitty\";'
\o
\! psql -f tmp.tmp
select now();
commit;
--
Jeff Boes Vox 269-226-9550 x24
Director of Software Development Fax 269-349-9076
Exfacto! Exceptional Online Content http://www.exfacto.com
Nexcerpt ...Extend Your Expertise... http://www.nexcerpt.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP TYPE without error?
@ 2005-04-07 20:24 Craig Addleman <craiga-@-sharechive.com>
parent: Philippe Lang <philippe.lang@attiksystem.ch>
2 siblings, 0 replies; 5+ messages in thread
From: Craig Addleman @ 2005-04-07 20:24 UTC (permalink / raw)
To: pgsql-sql
I was confronted with a similar problem. I have several scripts which create
or modify schemas, and each run in a single transaction. So, dropping a
non-existent TYPE will produce a show-stopping error. I wrote this function,
and others for various database objects:
CREATE OR REPLACE FUNCTION dba_droptype(varchar) RETURNS boolean AS '
DECLARE
p_type ALIAS FOR $1;
v_exists boolean;
BEGIN
SELECT INTO v_exists TRUE WHERE EXISTS(
SELECT 1 FROM pg_type
WHERE typname = p_type::name);
IF v_exists THEN
RAISE NOTICE ''Dropping TYPE %'', p_type;
EXECUTE ''DROP TYPE '' || p_type || '' CASCADE'';
END IF;
RETURN FOUND;
END;
' LANGUAGE 'plpgsql';
COMMENT ON FUNCTION dba_droptype(varchar) IS '
Usage: SELECT dba_drop_type(type_name)
Checks for existence of a type and drops it if found.
Implements DROP TYPE CASCADE; if a function or other object
depends on the type, that object will also be dropped.
Returns TRUE if successful, returns FALSE if type is
not found.';
--
Craig Addleman
DBA
ShareChive LLC
* Philippe Lang <philippe.lang@attiksystem.ch> [2005-04-06 05:59]:
> Hi,
>
> Since it is not possible to use CREATE OR REPLACE TYPE, is there a way
> of using DROP TYPE on a non-existing type, without causing the entire
> script to abort? It may sound crazy to ask for this, but it could be
> really useful in my case, where Pl/Pgsql and Pl/Perl code is being
> generated automatically, based on data found in a database.
>
> Thanks
>
> -----------------
> Philippe Lang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-07 20:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-06 12:59 DROP TYPE without error? Philippe Lang <philippe.lang@attiksystem.ch>
2005-04-06 20:03 ` Mischa <mischa.Sandberg@telus.net>
2005-04-07 12:37 ` Harald Fuchs <use_reply_to@protecting.net>
2005-04-07 19:48 ` Jeff Boes <jboes@nexcerpt.com>
2005-04-07 20:24 ` Craig Addleman <craiga-@-sharechive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox