Getting ERROR while running ALTER statement inside the SQL function

Question

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

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
– Brad, Commented Sep 14, 2021 at 17:13
Can you create a db-fiddle 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 db-fiddle.com/f/q9jynKQV3euZh9TPoW8GsH/0 — Alex Yu
– Alex Yu, Commented Sep 14, 2021 at 17:36
@Brad: you can absolutely use dynamic SQL in a stored function in Postgres — user330315
– user330315, Commented Sep 14, 2021 at 17:46

user330315 · Accepted Answer · 2021-09-14 17:48:27Z

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;

answered Sep 14, 2021 at 17:48

user330315

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

Collectives™ on Stack Overflow

Getting ERROR while running ALTER statement inside the SQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related