Do not reject entire statement when deleting rows in batch

I have a table who's purpose is to store the application's various images. It is defined this way :

CREATE TABLE images
(
  id lo NOT NULL,
  name character varying(1024) NOT NULL,
  type character varying(32) NOT NULL,
  dimension point,
  last_modified timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT "PK_images" PRIMARY KEY (id)
)

Some other tables have foreign keys referencing images.id with constraints that an image should not be deleted if it is referenced somewhere (i.e. the app is responsible to properly delete the reference first);

...
CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
   REFERENCES images (id) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE RESTRICT
...

Since many table rows may reference the same image, I need to automatically cleanup images that no longer have any reference.

My first idea was to implement the application to delete all foreign key references, then remove all images matching the values of these deleted foreign keys. To do so, I'd create a trigger on the table images, before delete, that could "swallow" all constraint exception if a query such as only "orphaned" images would be deleted and the others would be silently ignored. I'm just not sure how to properly do that.

If that idea is not good, what could be other (if not better) alternatives?

Basically, if I'm executing a query like

DELETE FROM images WHERE id IN (101, 102, 103, 104, 105)

and images.id's 102 and 104 have constraints that prevents them to be deleted, I'd like all other images to still be deleted. The DELETE query should be simple as the one above.

Thank you.

I was able to make this work with the below setup
Process:

1. Loop through the pg_constraint table and understand the constraints defined on the table that has to delete a record
2. Before deleting a record verify no reference record exists in any of the dependent table

Detailed implementation can be found below.
Let me know your thoughts on this.

```
#1: DDL

CREATE TABLE core.images
(
id serial NOT NULL,
name character varying(1024) NOT NULL
CONSTRAINT "PK_images" PRIMARY KEY (id)
);

create table core.app_images
(
id serial,
image_id int,
CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
REFERENCES core.images (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
);

create table core.app_images1
(
id serial,
image_id int,
CONSTRAINT "FK_foo_image1" FOREIGN KEY (image_id)
REFERENCES core.images (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)

#2: Funciton to check ref data integrity

CREATE OR REPLACE FUNCTION core.check_ref_exists (
p_schema TEXT,
p_table TEXT,
p_column TEXT,
p_value TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
r record ;
v_sql TEXT ;
v_return BOOLEAN DEFAULT FALSE ;
BEGIN
FOR r IN (
SELECT
pn.nspname s_name,
r_tab.relname AS t_name,
pa.attname AS c_name
FROM
pg_constraint pc
INNER JOIN pg_class tab ON tab.oid = pc.confrelid
INNER JOIN pg_class r_tab ON r_tab.oid = pc.conrelid
INNER JOIN pg_namespace pn ON pn.oid = r_tab.relnamespace
INNER JOIN pg_namespace pn_drv ON pn_drv.oid = tab.relnamespace
INNER JOIN pg_attribute pa ON (
pa.attrelid = pc.conrelid
AND pa.attnum = ANY (pc.conkey)
)
INNER JOIN pg_attribute pa_d ON (
pa_d.attrelid = pc.confrelid
AND pa_d.attnum = ANY (pc.confkey)
)
WHERE
contype = 'f'
AND tab.relname = p_table
AND pn_drv.nspname = p_schema
AND pa_d.attname = p_column
) loop

v_sql := 'SELECT TRUE FROM ' || p_schema || '.' || p_table || ' s
WHERE s. ID = ' || p_value || '
AND EXISTS (
SELECT 1 FROM ' || r.s_name || '.' || r.t_name || ' d WHERE s.' || p_column || ' = d.' || r.c_name || ')' ; EXECUTE v_sql INTO v_return ;
v_return := COALESCE (v_return, FALSE) ;
IF v_return = TRUE THEN
RETURN v_return ;
END IF ;

END loop ;
RETURN v_return ;
END ; $$

#3: Trigger Function
CREATE OR REPLACE FUNCTION core.images_tr_func()
RETURNS TRIGGER AS
$BODY$
DECLARE
v_ref_exists boolean;
BEGIN
select core.check_ref_exists (
p_schema := 'core',
p_table :='images',
p_column :='id',
p_value := old.id::text
) into v_ref_exists;

if v_ref_exists then
return null;
else
return old;
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

#4: Trigger
DROP TRIGGER if exists images_tr ON core.images;

CREATE TRIGGER images_tr BEFORE DELETE
ON core.images FOR EACH ROW
EXECUTE PROCEDURE core.images_tr_func();

#5: Sample Data
insert into core.images(name) values('image1'),('image2'),('image3'),('image4');
select * from core.images;

insert into core.app_images(image_id) values
(1),(1),(2)

select * from core.app_images

insert into core.app_images1(image_id) values
(2),(3),(3)

select * from core.app_images1

#6: Testing
delete from core.

StackExchange Database Administrators Q#107668, answer score: 3