Обсуждение: How to delete all operators
Hi, I have a database with operators and functions in plpgsql. To update the data to the latest version I would like to drop all operators. There might be some, that I don't know. I don't have access to the database, but have to write a script, that will update the data. Is there any way to drop all operators (given they are all in the schema "public) in a script? Something like (pseudocode): Drop all operators in schema "public" Thanks in advance for answers -- E-Mails und Internet immer und �berall! 1&1 PocketWeb, perfekt mit GMX: http://www.gmx.net/de/go/pocketweb
You can make a function to do this.
CREATE FUNCTION drop_operators(text) RETURNS BOOL AS
$$
DECLARE
op record;
BEGIN
FOR op IN SELECT opname
FROM pg_operator as o left join pg_namespace as n on (o.oprnamespace = n.oid)
WHERE nspname = $1
LOOP
EXECUTE 'DROP OPERATOR '||quote_literal(op.opname)|| ';';
END LOOP;
RETURN TRUE;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
And so: SELECT drop_operators( 'public');
--
William Leite Araújo
CREATE FUNCTION drop_operators(text) RETURNS BOOL AS
$$
DECLARE
op record;
BEGIN
FOR op IN SELECT opname
FROM pg_operator as o left join pg_namespace as n on (o.oprnamespace = n.oid)
WHERE nspname = $1
LOOP
EXECUTE 'DROP OPERATOR '||quote_literal(op.opname)|| ';';
END LOOP;
RETURN TRUE;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
And so: SELECT drop_operators( 'public');
On 3/31/06, Martin Pohl <Nilpherd@gmx.net> wrote:
Hi,
I have a database with operators and functions in plpgsql.
To update the data to the latest version I would like to drop all operators.
There might be some, that I don't know. I don't have access to the database,
but have to write a script, that will update the data.
Is there any way to drop all operators (given they are all in the schema
"public) in a script?
Something like (pseudocode):
Drop all operators in schema "public"
Thanks in advance for answers
--
E-Mails und Internet immer und überall!
1&1 PocketWeb, perfekt mit GMX: http://www.gmx.net/de/go/pocketweb
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
William Leite Araújo
On Mar 31, 2006, at 5:17 AM, Martin Pohl wrote: > Is there any way to drop all operators (given they are all in the > schema > "public) in a script? > > Something like (pseudocode): > Drop all operators in schema "public" Nope, though information_schema or the newsysviews project on pgFoundry might make it easier to get that list of operators. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461