4 ms·
Something like this (adjust the primary key name convention and schema as needed): create procedure mandatory_ (_table_name "text", _mandatory_table_name "te
by qazxcvbnm 4y ago
Something like this (adjust the primary key name convention and schema as needed):
create procedure mandatory_ (_table_name "text", _mandatory_table_name "text") language plpgsql as $$
declare
_trigger_name "text";
begin
_trigger_name = _table_name || '_mandatory_' || _mandatory_table_name || '_trigger';
execute
'create function "' || _trigger_name || '_" () returns trigger language plpgsql as $' || '$ '
'begin '
'if not exists '
'( select 1 '
'from public."' || _mandatory_table_name || '" _mandatory '
'where _mandatory ."' || _table_name || '_id" = new ."id" ) '
'then '
'raise '
'''non-existent relation between "%" and "%" violates mandatory participation constraint'' '
', ' || quote_literal (_table_name) ||
', ' || quote_literal (_mandatory_table_name) || '; '
'end if;'
'return new; '
'end; '
'$' || '$ ';
execute
'create constraint trigger "' || _trigger_name || '" '
'after insert or update on "' || _table_name || '" '
'deferrable initially deferred '
'for each row execute function "' || _table_name || '_mandatory_' || _mandatory_table_name || '_trigger_" () ';
end;
$$;