Your problem is in your design. If you do it like this:
CREATE TABLE A ( p_col serial PRIMARY KEY, acol integer ); CREATE TABLE B() INHERITS (A); INSERT INTO A(acol) VALUES (1); INSERT INTO B(acol) VALUES (2); SELECT * FROM A; SELECT * FROM B; Then the sequence (p_col) will be UNIQUE across all tables and can be referenced. No need for a key table. On Tue, Jun 2, 2015 at 3:45 PM, Andreas Ulbrich < andreas.ulbr...@matheversum.de> wrote: > On 02.06.2015 16:20, Melvin Davidson wrote: > > You can use the following to list the triggers and see what functions they > call. Then you can check pg_proc to see how TRUNCATE is used in prosrc. > > > SELECT c.relname, > t.tgname, > p.proname AS function_called, > t.tgconstraint AS is_constraint, > CASE WHEN t.tgconstrrelid > 0 > THEN (SELECT relname > FROM pg_class > WHERE oid = t.tgconstrrelid) > ELSE '' > END AS constr_tbl, > t.tgenabled > FROM pg_trigger t > INNER JOIN pg_proc p ON ( p.oid = t.tgfoid) > INNER JOIN pg_class c ON (c.oid = t.tgrelid) > WHERE tgname NOT LIKE 'pg_%' > AND tgname NOT LIKE 'RI_%' -- < comment out to see constraints > -- AND t.tgenabled = FALSE > ORDER BY 1; > > > On Tue, Jun 2, 2015 at 5:31 AM, Albe Laurenz <laurenz.a...@wien.gv.at> > wrote: > >> Andreas Ulbrich wrote: >> > I'm in a handle for a trigger for TRUNCATE. Is it possible to find out >> > whether the TRUNCATE TABLE ist called with CASCADE? >> >> I don't think there is. >> >> But you can find out the table where the trigger is defined and examine >> if any foreign key constraints are referring to it. >> >> If yes, then the trigger was called with CASCADE. >> If no, it might have been called either way, but the effect would be the >> same. >> >> Yours, >> Laurenz Albe >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > I think, I must explain the problem deeper: > > I have two (or more) tables > CREATE TABLE a (id ... UNIQUE -- maby the PRIMARY KEY,...); > CREATE TABLE b (...) INHERIT (a); > > But the id has to be unique over the inheritance. So one solution of the > problem is: > CREATE key_table (id ... UNIQUE, table REGCLASS); > By trigger every INSERT/UPDATE/DELETE in Table a,b,... changes the > key_table. > This works. > > Now I have a table reference to the id of table a*. This is not possible, > but reference to key_table(id) works fine. > CREATE TABLE r (..., a_id /* REFERENCES a*(id) */ REFERENCES > key_tabel(id),..); > > And now the problem: Can I support TRUNCATE TABLE? > DELETE is not a problem: for DELETE FROM a the trigger deletes the entry > in the key_table and if the reference action on delete is CASCADE, the > entries in r will be deletet. > > But TRUNCATE TABLE a! In a TRUNCATE TRIGGER I can delete the entries in > the key_table "WHERE table = a" (O.K. the performance) -- it is actual not > a TRUNCATE TABLE but a TRUNCATE PARTITION. > And if I not specified ONLY, there is also a TRUNCATE TABLE b and the > trigger ist fired too. > > But what is with table r? If I do the delete in the key_table, the delete > action will be used. But there is not a truncate action, cascaded > truncation is controlled by execute TRUNCATE. And so, I must delete the > entries in r if there is a CASCADE in the TRUNCATE or raise an exception if > the TRUNCATE is RESTRICTED. > > Now the Question? How to find out in the trigger function for truncate > whether is there a CASCADE or not. > > regards, Andreas > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. > > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
