How to reference postgres variables as variables, not as literals

Question

I'm highly confused about the use of variables in postgres. From the psql command-line, I've executed:

DO $$
DECLARE
  NEW_SCHEMA          TEXT:= 'myschema';
BEGIN
  RAISE NOTICE 'Value of NEW_SCHEMA: %', NEW_SCHEMA;  -- A

  DROP SCHEMA IF EXISTS NEW_SCHEMA;               -- B
  CREATE SCHEMA IF NOT EXISTS NEW_SCHEMA;         -- C

END$$;

Gives me:

NOTICE:  Value of NEW_SCHEMA: myschema
NOTICE:  schema "new_schema" does not exist, skipping
DO

... so... at A, NEW_SCHEMA is interpreted as a variable, but at B and C, it is interpreted as a literal, and a new schema called NEW_SCHEMA is created.

How do I can I get psql to interpret variables as variables, not as literals.

You need dynamic SQL for that.

user330315
– user330315

2018-12-03 21:27:24 +00:00
Commented Dec 3, 2018 at 21:27 — user330315
– user330315, Commented Dec 3, 2018 at 21:27

ben grimer · Accepted Answer · 2018-12-03 21:45:56Z

1

It looks like you cannot use a variable with the DROP SCHEMA command. You have to construct some SQL Dynamically. The answer seems to be given here. No point in reinventing the wheel. :-)

Drop a schema using variable name

Update: I tried the following using Dynamic SQL and it works. My conclusion is that the CREATE/DROP SCHEMA does not allow variables. I commented out the original lines to show the dynamic replacements.

do $$
DECLARE
  NEW_SCHEMA TEXT:= 'myschema';
--  SQL TEXT := '';
BEGIN
  RAISE NOTICE 'Value of NEW_SCHEMA: %', NEW_SCHEMA; -- A

--  DROP SCHEMA IF EXISTS NEW_SCHEMA;                  -- B
--  CREATE SCHEMA IF NOT EXISTS NEW_SCHEMA;      -- C

  EXECUTE 'DROP SCHEMA IF EXISTS ' || NEW_SCHEMA;
  EXECUTE 'CREATE SCHEMA IF NOT EXISTS ' || NEW_SCHEMA;

END$$;

edited Dec 3, 2018 at 21:45

answered Dec 3, 2018 at 20:56

ben grimer

284 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user48956 Over a year ago

This refers to sql-server, not Postgres

ben grimer Over a year ago

Good point (I did not notice that), but that does not mean it does not apply to Postgres also. Have you tried ? :-)

user48956 Over a year ago

Good grief... when the cure is worse than the disease. Copy and paste it is then. :-/ Thanks.

user48956 Over a year ago

My apologies -- and thanks for the reply. I'm grumbling at (yet another) linguistic horror of SQL, not your solution. I was hoping to use variables to clean up and modularize a messy installation script. Again, SQL fights you all the way. Hmmppph.

404 Over a year ago

FYI it's nothing to do with CREATE/DROP commands specifically; you cannot use a variable for an identifier, i.e. a schema, table, column, etc. If you want to do that, then you will always need dynamic sql.

|

Collectives™ on Stack Overflow

How to reference postgres variables as variables, not as literals

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related