Thread: "too clever" when creating SQL functions
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 ------------------------------
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
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
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.
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.
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';
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';