0

I wrote a SQL function to ALTER the SCHEMA Ownership. I am able to run the statement directly(without function) but getting error while running the ALTER statement within the function.

Function "test" created successfully:

CREATE OR REPLACE FUNCTION test.update_ownership(schemaname text) RETURNS text AS $$ 
DECLARE
        BEGIN
        ALTER SCHEMA schemaname OWNER TO postgresql;
        END;
$$ LANGUAGE plpgsql;

Executing the function as:

select test.update_ownership('myschema')

I am getting an ERROR while executing the function i.e.:

ERROR: schema "schemaname" does not exist

CONTEXT: SQL statement "ALTER SCHEMA schemaname OWNER TO postgresql" PL/pgSQL function collection.update_ownership(text) line 4 at SQL statement SQL state: 3F000

Anmol
  • 1
  • 2
  • You are passing the schemaname as variable? In your code (and error) it is saying your variable as text (not using variable value). In MSSQL you cant do that, you would need to use dynamic SQL and not sure if that works in postgressql. – Brad Sep 14 '21 at 17:13
  • Can you create a [db-fiddle](https://www.db-fiddle.com/) with such function ? Because I see "Schema Error: error: unterminated dollar-quoted string at or near "$$ DECLARE BEGIN ALTER SCHEMA schemaname OWNER TO postgresql;"" on https://www.db-fiddle.com/f/q9jynKQV3euZh9TPoW8GsH/0 – Alex Yu Sep 14 '21 at 17:36
  • 1
    @Brad: you can absolutely use dynamic SQL in a stored function in Postgres –  Sep 14 '21 at 17:46

1 Answers1

2

You can't use parameters as identifiers, you have to use dynamic SQL for this. Generating the SQL is best done using the format() function to properly deal with identifiers.

CREATE OR REPLACE FUNCTION test.update_ownership(schemaname text) 
   RETURNS void 
AS $$ 
BEGIN
  execute format('ALTER SCHEMA %I OWNER TO postgresql', schemaname);
END;
$$ LANGUAGE plpgsql;