CREATE OR REPLACE FUNCTION select_fk(
search_table varchar(30),
name_id varchar(30),
id int
) RETURNS SETOF boolean AS $$
DECLARE
s_record record;
e_sql text;
c_sql text;
s_sql text;
name_seq text;
delete_sql text;
count_tables int;
sub_id INT;
integer_var INT;
result boolean;
BEGIN
result = false;
RAISE NOTICE '-- START table="%" name_id="%" id="%" --', search_table, name_id, id;
CREATE temporary table IF NOT EXISTS tmp_tables(table_name varchar(255)) on commit drop;
FOR s_record IN
SELECT
tc.table_schema AS foreign_table_schema,
tc.constraint_name,
tc.table_name AS foreign_table_name,
kcu.column_name AS foreign_column_name,
ccu.table_schema AS table_schema,
ccu.table_name,
ccu.column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND ccu.table_name=search_table
LOOP
e_sql = format ('SELECT count(*) from tmp_tables WHERE table_name = %L', s_record.foreign_table_name );
-- RAISE NOTICE 'e_sql = %', e_sql;
EXECUTE e_sql INTO count_tables;
-- RAISE NOTICE 'count_tables = %', count_tables;
IF count_tables > 0 THEN
-- RAISE NOTICE ' ---! in array true --- ';
ELSE
-- RAISE NOTICE 'f_table_schema = %; f_table_name = % f_column_name = %',
-- s_record.foreign_table_schema,
-- s_record.foreign_table_name,
-- s_record.foreign_column_name
-- ;
c_sql = format ('SELECT isc.column_name ' ||
'FROM information_schema.sequences as iss, information_schema.columns as isc' ||
' WHERE iss.sequence_name = %L ' ||
' and isc.table_name = %L ' ||
' and isc.column_default like %L',
s_record.foreign_table_name || '_id_seq',
s_record.foreign_table_name,
'%' || s_record.foreign_table_name || '_id_seq%'
);
-- RAISE NOTICE '-1 % -- ', c_sql;
EXECUTE c_sql INTO name_seq;
-- RAISE NOTICE 'seq = %', name_seq;
IF name_seq IS NOT NULL THEN
s_sql = format ('SELECT %I FROM %I WHERE %I = %s',
name_seq,
s_record.foreign_table_name,
s_record.foreign_column_name,
id
);
-- RAISE NOTICE '-2 % -- ', s_sql;
EXECUTE s_sql INTO sub_id;
-- RAISE NOTICE '!! % !! ', sub_id;
IF sub_id>0 THEN
-- RAISE NOTICE '-- % -- table="%" column="%"', sub_id, s_record.table_name,s_record.foreign_column_name;
INSERT INTO tmp_tables VALUES (s_record.foreign_table_name);
EXECUTE format(
'SELECT select_fk(%L, %L, %s)',
s_record.foreign_table_name,
name_seq,
sub_id) INTO result;
ELSE
delete_sql = format(
'DELETE FROM %I WHERE %I = %s;',
s_record.foreign_table_name,
s_record.foreign_column_name,
id);
RAISE NOTICE 'SUB delete_sql=%', delete_sql;
EXECUTE format('LOCK TABLE %I IN EXCLUSIVE MODE' , s_record.foreign_table_name);
BEGIN
EXECUTE delete_sql;
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'ERROR_sql=%', delete_sql;
-- don't worry if it already exists
END;
GET DIAGNOSTICS integer_var = ROW_COUNT;
RAISE NOTICE 'integer_var=%', integer_var;
END IF;
END IF;
END IF;

END LOOP;

delete_sql = format(
'DELETE FROM %I WHERE %I = %s;',
search_table,
name_id,
id);
RAISE NOTICE 'PRM delete_sql=%', delete_sql;
EXECUTE format('LOCK TABLE %I IN EXCLUSIVE MODE' , search_table);
EXECUTE delete_sql;
GET DIAGNOSTICS integer_var = ROW_COUNT;
RAISE NOTICE 'integer_var=%', integer_var;

RAISE NOTICE '-- END table="%" --', search_table;

RETURN NEXT result;

END $$ LANGUAGE plpgsql VOLATILE;

select select_fk('erp_clients', 'id', 6579 );
DROP FUNCTION IF EXISTS select_fk(character varying,character varying,integer);