Glen W. Mabey wrote:
On Mon, Mar 19, 2007 at 04:51:57PM +0100, hubert depesz lubaczewski wrote:
On 3/19/07, Glen W. Mabey <Glen.Mabey@xxxxxxxx> wrote:
I'm using 8.1.8, and I have a situation where a record in one table
is
only meaningful when it is referenced via foreign key by one or more
records in any one of several tables.
So, really what I want is when one of the referring records is
deleted,
to have a trigger check to see if it was the last one to use that
foreign key, and if so, to delete that other record, too.
My first implementation of this functionality was to write a trigger
function that executed a COUNT(*) on all of the tables that could
have a
reference in them. That became way too slow for the number of
records
in these tables.
Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the
foreign
key constraint, and then trying to catch the exception thrown when a
deletion attempt is made on the record. However, it seems that this
PL/pgsql snippet fails to catch such an error:
BEGIN EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;
But, really, I just want to be able to test to see how many
references there are to a key. Is there
some way to do that?
write a triggers which do that.
I understand that a trigger should be written, and I have already
implemented two such triggers, as described above.
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.
This query will return the list of foreign keys which refer to primary keys:
SELECT
g as "DB",n.nspname as "PK_schema",pc.relname as
"PK_table",pa.attname as "PK_column",
n.nspname as "FK_schema",c.relname as "FK_table",a.attname as
"FK_column",b.n as "FK_column_number", f.conname as "FK_name",
pr.conname as "PK_name"
FROM
current_database()g,pg_catalog.pg_attribute a,pg_catalog.pg_attribute
pa,pg_catalog.pg_class c,pg_catalog.pg_class pc,pg_catalog.pg_namespace n,
pg_catalog.pg_namespace pn,pg_catalog.pg_constraint f left join
pg_catalog.pg_constraint pr on(f.conrelid=pr.conrelid and pr.contype='p'),
(SELECT * FROM
generate_series(1,current_setting('max_index_keys')::int,1))b(n)
WHERE
n.oid=c.relnamespace AND pn.oid=pc.relnamespace AND pc.oid=f.confrelid
AND c.oid=f.conrelid AND pa.attrelid=f.confrelid AND a.attrelid=f.conrelid
AND pa.attnum=f.confkey[b.n]AND a.attnum=f.conkey[b.n]AND
f.contype='f'AND f.conkey[b.n]<>0 AND has_schema_privilege(n.oid,
'USAGE'::text);
Add conditions to the pr.conname and you will get what you need
