CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sqlddl
Anmol
Anmol

Reputation: 1

Getting ERROR while running ALTER statement inside the SQL function

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

Upvotes: 0

Views: 345

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions