I have a template of a migration script that we run with new version when needed. It’s something like:
x
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;
$$;