Error when setting n_distinct using a plpgsql variable

Question

I tried to use a function to set the n_distinct value for a table. The code is as follows:

create temporary table _temp
(
  id integer
);

create function pg_temp.setdistinct(_cnt real)
returns void as $$
begin
  alter table _temp
    alter column id set (n_distinct=_cnt);
end
$$ language plpgsql;

select pg_temp.setdistinct(1000);

Yet receive the following errors:

ERROR:  invalid value for floating point option "n_distinct": _cnt
CONTEXT:  SQL statement "alter table _temp
         alter column id set (n_distinct=_cnt)"
PL/pgSQL function pg_temp_3.setdistinct(real) line 3 at SQL statement

The issue can be bypassed using an EXECUTE statement, but I wonder why we can't use a variable in this particular query. Is there any particular rule I overlooked?

Fuqua

Answer 1

This is by design. The manual explains in the chapter Variable Substitution:

Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE 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 and EXECUTE 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, and DELETE 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.

Keramic