I have a template of a migration script that we run with new version when needed. It’s something like:
SET "myapp.user"='someuser'; --This line CANNOT change DROP TABLE IF EXISTS backup_tables.someuser_parameter; CREATE TABLE backup_tables.someuser_parameter AS SELECT * FROM "public".parameter; INSERT INTO "public".parameter (key, value, enabled) SELECT 'task_name', ( '{"worker_config": {"crontab": {"hour": "0", "minute": "1"}, "disabled": false, "periodic_task": true, "username": "' || current_setting('myapp.user') || '"}}' )::JSONB, TRUE;
As you can see, the myapp.user value (someuser) is used at three places in the script.
- Is there a more elegant way to use myapp.user in the INSERT?
- How can I use myapp.user in the DROP TABLE and the CREATE TABLE statements?
I use PostgreSQL 9.4.14 if it changes something.
Advertisement
Answer
SET "myapp.user"='someuser'; --This line CANNOT change do language plpgsql $$ declare myapp_user text := current_setting('myapp.user'); begin execute format('DROP TABLE IF EXISTS backup_tables.%I', myapp_user); execute format('CREATE TABLE backup_tables.%I AS SELECT * FROM "public".parameter', myapp_user); INSERT INTO "public".parameter (key, value, enabled) SELECT 'task_name', ( '{"worker_config": {"crontab": {"hour": "0", "minute": "1"}, "disabled": false, "periodic_task": true, "username": "' || myapp_user || '"}}' )::JSONB, TRUE; end; $$;