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.images where id = 1; --Nothing happened as it is still referred
            delete from core.images where id = 4; --Deleted as it is an orphan record