Error when setting n_distinct using a plpgsql variable

by

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.

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Truncating all tables in a Postgres database
  3. Execute a dynamic crosstab query
  4. Dynamic SQL (EXECUTE) as condition for IF statement
  5. Test for null in function with varying parameters
  6. Return SETOF rows from PostgreSQL function
  7. Dynamically generate columns for crosstab in PostgreSQL
  8. Function to loop through and select data from multiple tables
  9. Format specifier for integer variables in format() for EXECUTE?
  10. Table name as a PostgreSQL function parameter
  11. What are ‘$$’ used for in PL/pgSQL
  12. The forgotten assignment operator “=” and the commonplace “:=”
  13. Define table and column names as arguments in a plpgsql function?
  14. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  15. dynamic sql query in postgres
  16. Is there a way to disable function overloading in Postgres
  17. Can I make a plpgsql function return an integer without using a variable?
  18. PostgreSQL function returning multiple result sets
  19. User defined variables in PostgreSQL
  20. SELECT raises exception in PL/pgSQL function
  21. DROP FUNCTION without knowing the number/type of parameters?
  22. Postgres FOR LOOP
  23. Use variable set by psql meta-command inside of DO block
  24. COPY with dynamic file name
  25. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  26. SQL Sub queries in check constraint
  27. Cursor based records in PostgreSQL
  28. Loop on tables with PL/pgSQL in Postgres 9.0+
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. Loop through columns of RECORD

Leave a Comment