On Mon, 2007-03-19 at 11:12 -0500, Glen W. Mabey wrote: > What I'm hoping to find out is whether there is some way to directly > find out how many (using a SELECT query) references there are to a > key. In the easy case when your schema doesn't change often, you can just hard code a query of the FK tables and add up the row counts. I bet something like 'select count(*) from (select * from FKtable1 UNION ALL select * from FKtable2 ... )' will work (and I'm guessing that the UNION ALL will optimize well). Obviously, you want indexes on the FKs. The harder and more general case is to build such a query dynamically from pg_depends. A good start would be to write a function that returns an sql query like the above to count the referents of PKtable(PKcolumn). If you can declare this function stable or immutable (I'm not sure of this), then it might not be too painful to generate the query within the trigger itself. Otherwise, you might have to store/update these queries in a separate table after every DDL change. See the pg_depends documentation at http://www.postgresql.org/docs/8.2/interactive/catalog-pg-depend.html -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter.
