creating schemas inside functions on PostgreSQL-CodePudding

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?

CodePudding user response：

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;
$$