Thread: DROP TYPE without error?
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
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."
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.
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
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_existsTHEN 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