Error when setting n_distinct using a plpgsql variable
Solution 1:
This is by design. The manual explains in the chapter Variable Substitution:
Variable substitution currently works only in
SELECT
,INSERT
,UPDATE
, andDELETE
commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the utility statement as a string andEXECUTE
it.
You cannot parameterize the value in a dynamic statement with EXECUTE
either because, quoting the chapter Executing Dynamic Commands:
Another restriction on parameter symbols is that they only work in
SELECT
,INSERT
,UPDATE
, andDELETE
commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.
The only option in a plpgsql function is to concatenate the value into the command string. You might use format()
, but for the simple example, plain concatenation is safe and easy::
CREATE OR REPLACE FUNCTION pg_temp.setdistinct(_cnt real)
RETURNS void
LANGUAGE plpgsql AS
$$
BEGIN
EXECUTE 'ALTER TABLE _temp ALTER COLUMN id SET (n_distinct=' || _cnt || ')';
END
$$;
The schema-qualification pg_temp.
makes it an (undocumented!) "temporary" function, mirroring the question.
The manual about n_distinct
.