pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"too clever" when creating SQL functions
7+ messages / 3 participants
[nested] [flat]
* "too clever" when creating SQL functions
@ 2003-05-07 17:51 SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 7+ messages in thread
From: SZŰCS Gábor @ 2003-05-07 17:51 UTC (permalink / raw)
To: pgsql-sql
Dear Gurus,
Please show me to the archives, if you feel like. I have an ugly function
that can't be defined as language SQL, because it gets parsed at
define-time, not run-time. Any ideas, other than making it plpgsql?
Even better, is there a more elegant way to turn off/on triggers on a (set
of) table(s)?
The function and what psql says is at the end of this message.
G.
--
while (!asleep()) sheep++;
---------------------------- cut here ------------------------------
bin=# CREATE OR REPLACE FUNCTION triggers_on(name) RETURNS int AS '
bin'# CREATE TEMP TABLE tr (tmp_relname name, tmp_reltriggers smallint);
bin'#
bin'# INSERT INTO tr
bin'# SELECT C.relname, count(T.oid)
bin'# FROM pg_class C, pg_trigger T
bin'# WHERE C.oid = T.tgrelid AND C.relname ~* $1
bin'# GROUP BY 1;
bin'# UPDATE pg_class SET reltriggers = TMP.tmp_reltriggers
bin'# FROM tr TMP
bin'# WHERE pg_class.relname = TMP.tmp_relname;
bin'#
bin'# DROP TABLE tr;
bin'# SELECT 1;
bin'# ' LANGUAGE 'SQL';
ERROR: Relation "tr" does not exist
---------------------------- cut here ------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "too clever" when creating SQL functions
@ 2003-05-07 19:10 Richard Huxton <dev@archonet.com>
parent: SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2003-05-07 19:10 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; pgsql-sql
On Wednesday 07 May 2003 6:51 pm, SZŰCS Gábor wrote:
> Dear Gurus,
>
> Please show me to the archives, if you feel like. I have an ugly function
> that can't be defined as language SQL, because it gets parsed at
> define-time, not run-time. Any ideas, other than making it plpgsql?
>
> Even better, is there a more elegant way to turn off/on triggers on a (set
> of) table(s)?
You might want to look at the output of pg_dump - IIRC that disables all your
triggers (fk's etc) and re-enables them at the end. Might be what you're
after.
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "too clever" when creating SQL functions
@ 2003-05-08 11:15 SZŰCS Gábor <surrano@mailbox.hu>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: SZŰCS Gábor @ 2003-05-08 11:15 UTC (permalink / raw)
To: pgsql-sql
Dear Richard,
I had a look at a 7.2.1 dump, but it doesn't do that thing. It simply
creates triggers after all the data has been COPY-ed (i.e. at the end of the
file. Only rules and sequence setvals after that.
G.
--
while (!asleep()) sheep++;
---------------------------- cut here ------------------------------
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
Sent: Wednesday, May 07, 2003 9:10 PM
You might want to look at the output of pg_dump - IIRC that disables all
your
triggers (fk's etc) and re-enables them at the end. Might be what you're
after.
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "too clever" when creating SQL functions
@ 2003-05-08 14:53 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2003-05-08 14:53 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-sql
On Thu, 8 May 2003, [iso-8859-2] SZÛCS Gábor wrote:
> Dear Richard,
>
> I had a look at a 7.2.1 dump, but it doesn't do that thing. It simply
> creates triggers after all the data has been COPY-ed (i.e. at the end of the
> file. Only rules and sequence setvals after that.
It's only in data only dumps AFAIR.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "too clever" when creating SQL functions
@ 2003-05-08 16:41 SZŰCS Gábor <surrano@mailbox.hu>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 7+ messages in thread
From: SZŰCS Gábor @ 2003-05-08 16:41 UTC (permalink / raw)
To: pgsql-sql
didn't make it in data-only (-a) mode either.
G.
--
while (!asleep()) sheep++;
---------------------------- cut here ------------------------------
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
Sent: Thursday, May 08, 2003 4:53 PM
On Thu, 8 May 2003, [iso-8859-2] SZŰCS Gábor wrote:
> Dear Richard,
>
> I had a look at a 7.2.1 dump, but it doesn't do that thing. It simply
> creates triggers after all the data has been COPY-ed (i.e. at the end of
the
> file. Only rules and sequence setvals after that.
It's only in data only dumps AFAIR.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "too clever" when creating SQL functions
@ 2003-05-08 19:22 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2003-05-08 19:22 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-sql
On Thu, 8 May 2003, [iso-8859-2] SZÛCS Gábor wrote:
> didn't make it in data-only (-a) mode either.
I forgot to mention that it probably requires that there be foreign keys.
:)
But, it basically does:
-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'a';
COPY "a" FROM stdin;
\.
-- Enable triggers
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where
pg_class.oid = tgrelid) WHERE relname = 'a';
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "too clever" when creating SQL functions
@ 2003-05-09 15:31 SZŰCS Gábor <surrano@mailbox.hu>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 7+ messages in thread
From: SZŰCS Gábor @ 2003-05-09 15:31 UTC (permalink / raw)
To: pgsql-sql
István :)
This answers my question, thanks :)
G.
--
while (!asleep()) sheep++;
---------------------------- cut here ------------------------------
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
Sent: Thursday, May 08, 2003 9:22 PM
On Thu, 8 May 2003, [iso-8859-2] SZŰCS Gábor wrote:
> didn't make it in data-only (-a) mode either.
I forgot to mention that it probably requires that there be foreign keys.
:)
But, it basically does:
-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'a';
COPY "a" FROM stdin;
\.
-- Enable triggers
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where
pg_class.oid = tgrelid) WHERE relname = 'a';
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-05-09 15:31 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-07 17:51 "too clever" when creating SQL functions SZŰCS Gábor <surrano@mailbox.hu>
2003-05-07 19:10 ` Richard Huxton <dev@archonet.com>
2003-05-08 11:15 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-05-08 14:53 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-08 16:41 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-05-08 19:22 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-09 15:31 ` SZŰCS Gábor <surrano@mailbox.hu>
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