65 lines
1.8 KiB
PL/PgSQL
65 lines
1.8 KiB
PL/PgSQL
BEGIN;
|
|
|
|
DO $$
|
|
DECLARE
|
|
current_schema_name text := current_schema();
|
|
unique_constraint_name text;
|
|
unique_index_name text;
|
|
BEGIN
|
|
SELECT tc.constraint_name
|
|
INTO unique_constraint_name
|
|
FROM information_schema.table_constraints AS tc
|
|
JOIN information_schema.constraint_column_usage AS ccu
|
|
ON ccu.constraint_schema = tc.constraint_schema
|
|
AND ccu.constraint_name = tc.constraint_name
|
|
WHERE tc.table_schema = current_schema_name
|
|
AND tc.table_name = 'flow_task_instance'
|
|
AND tc.constraint_type = 'UNIQUE'
|
|
AND ccu.column_name = 'append_id'
|
|
ORDER BY tc.constraint_name
|
|
LIMIT 1;
|
|
|
|
IF unique_constraint_name IS NOT NULL THEN
|
|
EXECUTE format(
|
|
'ALTER TABLE %I.%I DROP CONSTRAINT %I',
|
|
current_schema_name,
|
|
'flow_task_instance',
|
|
unique_constraint_name);
|
|
END IF;
|
|
|
|
SELECT indexname
|
|
INTO unique_index_name
|
|
FROM pg_indexes
|
|
WHERE schemaname = current_schema_name
|
|
AND tablename = 'flow_task_instance'
|
|
AND indexname = 'IX_flow_task_instance_append_id'
|
|
AND indexdef LIKE '%UNIQUE INDEX%'
|
|
LIMIT 1;
|
|
|
|
IF unique_index_name IS NOT NULL THEN
|
|
EXECUTE format(
|
|
'DROP INDEX IF EXISTS %I.%I',
|
|
current_schema_name,
|
|
unique_index_name);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_indexes
|
|
WHERE schemaname = current_schema_name
|
|
AND tablename = 'flow_task_instance'
|
|
AND indexname = 'IX_flow_task_instance_append_id'
|
|
) THEN
|
|
EXECUTE format(
|
|
'CREATE INDEX %I ON %I.%I (%I)',
|
|
'IX_flow_task_instance_append_id',
|
|
current_schema_name,
|
|
'flow_task_instance',
|
|
'append_id');
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
COMMIT;
|
|
|