How to add value in enum in plpgsql function-CodePudding

So I have some simple function that should add value to an existing enum "profession" but instead of working it shows error

CREATE FUNCTION add_prof(p text) RETURNS VOID AS $$
    BEGIN
        ALTER TYPE profession ADD VALUE p;
        RETURN;
    END;
    $$ LANGUAGE plpgsql;

The error is "[42601] syntax error". Btw, DataGrip shows that "string or IF expected but p."

Changing p to ' ' of course works fine but that's not what I need.

CodePudding user response：

ALTER command is DDL command, and DDL commands doesn't allow an parametrization (they has not an execution plan). You need to use dynamic SQL:

CREATE FUNCTION add_prof(p text)
RETURNS VOID AS $$
BEGIN
  EXECUTE format('ALTER TYPE profession ADD VALUE %L', p);
  RETURN;
END;
$$ LANGUAGE plpgsql;