Storing schema name as a variable in postgresql

Question

I'm trying to avoid using PGSQL for some simple queries, but I want to store the schema name as a variable and use it later in multiple queries:

WITH p AS (SELECT 'testSchema' AS schemaName)
CREATE SCHEMA IF NOT EXISTS p.schemaName;
create table if not exists p.schemaName.table1;

Perhaps "with" is not the right way, or may by I need to use it differently.

Laurenz Albe · Accepted Answer · 2017-02-27 12:29:27Z

2

You should use the SQL statement SET, perhaps with the LOCAL option, but that won't work with CREATE SCHEMA.

Something like this:

BEGIN;  -- start transaction
CREATE SCHEMA IF NOT EXISTS testschema;
SET LOCAL search_path = 'testschema';  -- only for this transaction
CREATE TABLE IF NOT EXISTS table1 ...; -- will be created in testschema
COMMIT;

answered Feb 27, 2017 at 12:29

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

2 Comments

Guy s Over a year ago

I finally used: SET LOCAL search_path = 'schema1', 'schema2'; as i needed more than 1 schema in my script.

combinatorist Over a year ago

But, if for some reason the command line is more acceptable to you than pl/pgsql (dynamic sql procedures), then you could set schemas in create views with a variable: dba.stackexchange.com/a/172684/179926.

Collectives™ on Stack Overflow

Storing schema name as a variable in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related