How to use a query to set a custom option?

Question

I tried this but it does not work:

CREATE OR REPLACE FUNCTION answer()
 RETURNS bigint
 LANGUAGE sql AS
$$
SELECT 42
$$;
SET session.answer = select answer();

Answer 1

The SET command only accepts constants. The manual:

constants, identifiers, numbers, or comma-separated lists of these

It does not take parameters, nor can it evaluate subqueries. Parameter substitution only works for basic DML commands (SELECT, INSERT, UPDATE, DELETE, MERGE).

The function set_config() provides equivalent functionality;

SELECT set_config('session.answer', answer()::text, false);

Note the cast to ::text. Postgres parameters only store text.

Related:

• Set a temporary global variable during a Postgres query
• How to reuse a computed value multiple times?
• Set custom constant in Postgres script