Lists: | pgsql-general |
---|
From: | " Martin Pohl" <Nilpherd(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to delete all operators |
Date: | 2006-03-31 10:17:39 |
Message-ID: | 28786.1143800259@www062.gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | William Leite Araújo <william(dot)bh(at)gmail(dot)com> |
---|---|
To: | "Martin Pohl" <Nilpherd(at)gmx(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to delete all operators |
Date: | 2006-04-03 15:01:10 |
Message-ID: | bc63ad820604030801k249bb97el46ba70916f9ef3ba@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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');
On 3/31/06, Martin Pohl <Nilpherd(at)gmx(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
William Leite Araújo
From: | Jim Nasby <jnasby(at)pervasive(dot)com> |
---|---|
To: | Martin Pohl <Nilpherd(at)gmx(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to delete all operators |
Date: | 2006-04-03 17:09:11 |
Message-ID: | C6121D81-9FB1-44C2-B61B-71E0BC5AE661@pervasive.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461