Skip to content
Advertisement

How can I use a SET variable concisely in a DROP/CREATE/INSERT statements in PostgreSQL?

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.

  1. Is there a more elegant way to use myapp.user in the INSERT?
  2. 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;
$$;
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement