creating schemas inside functions on PostgreSQL

Question

I'm trying to create a schema after inserting a new "something" What is the correct syntax on PostgreSQL14 (or even older versions) that will give me the same as CREATE SCHEMA IF NOT EXISTS ischema where ischema is the function input, ischema?

CREATE OR REPLACE FUNCTION blah_insert_something(
    IN iname varchar,
    IN ischema varchar
) RETURNS UUID
    LANGUAGE plpgsql AS
$$
DECLARE
    result UUID;
BEGIN
    INSERT INTO internal.something(name, schema, data)
    VALUES ($1, $2, '{}')
    RETURNING id INTO result;

    CREATE SCHEMA IF NOT EXISTS "$2";

    RETURN result;
END;
$$

Currently it literally creates a schema called $2, if I remove the quotes or use single quotes, it complains about invalid syntax. If I put ischema in there and run

SELECT blah_insert_something('test', 'testing')

it literally creates a schema called ischema instead of testing.

What is the correct syntax to make this demo code work?

You need to use dynamic SQL with EXECUTE. Use the format() function. — Laurenz Albe
– Laurenz Albe, Commented Sep 17, 2022 at 22:27
Format works well with @nbk's solution: CREATE SCHEMA IF NOT EXISTS ' || format('prefix_%I', $2) || ';'; I will need to be very careful about SQL injection here — Jan Vladimir Mostert
– Jan Vladimir Mostert, Commented Sep 18, 2022 at 6:42

nbk · Accepted Answer · 2022-09-18 07:02:41Z

3

Your function makes not much sense but you need dynamic sql for that

CREATE OR REPLACE FUNCTION blah_insert_something(
    IN iname varchar,
    IN ischema varchar
) RETURNS UUID
    LANGUAGE plpgsql AS
$$
DECLARE
    result UUID;
    stmt text;
BEGIN
    INSERT INTO internal.something(name, schema, data)
    VALUES ($1, $2, '{}')
    RETURNING id INTO result;

     
     stmt = 'CREATE SCHEMA IF NOT EXISTS ' || $2 || ';';
    

     EXECUTE stmt;
    

    RETURN result;
END;
$$

edited Sep 18, 2022 at 7:02

user330315

answered Sep 17, 2022 at 22:29

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

2 Comments

Jan Vladimir Mostert Over a year ago

a table to track all "entities" which is inside the internal schema and then a schema for each entity so their data is completely separate

Jan Vladimir Mostert Over a year ago

I had to remove the IMMEDIATE part and I discovered that my semicolon is in the wrong place, it should be after the $$, not after END, otherwise this solution works, thanks!

Collectives™ on Stack Overflow

creating schemas inside functions on 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