flow_pkey.sql

DROP FUNCTION public.pkey_flows();
DROP FUNCTION public.add_pkey_flows();

CREATE OR REPLACE FUNCTION public.pkey_flows()
    RETURNS TABLE(table_access text, pkeyName text) AS
$body$
SELECT table_schema || '.' || table_name as table_access, table_name || '_pkey' as pkey
FROM information_schema.tables as t
WHERE
        t.table_name like 'flow_%'
    and t.table_schema = 'flows'
    and not exists (select constraint_name
                       from information_schema.constraint_column_usage
                       where table_name = t.table_name  and constraint_name = t.table_name || '_pkey')
$body$
    LANGUAGE sql;

select * from public.pkey_flows() ;

CREATE OR REPLACE FUNCTION public.add_pkey_flows()
    RETURNS void AS
$func$
DECLARE
    pkeyFlow text;
    pkey text;
BEGIN
    RAISE NOTICE 'Adding pkey on flows';
    FOR pkeyFlow, pkey IN (SELECT table_access, pkeyName FROM public.pkey_flows())
        LOOP
            RAISE NOTICE '  Add pkey on flow table: %', pkeyFlow;
            EXECUTE format('alter table %s add constraint %s primary key (mid)', pkeyFlow, pkey);
        END LOOP;
END
$func$ LANGUAGE plpgsql;

select * from public.add_pkey_flows() ;