How to set column DEFAULT using value of a variable

Question

In a script for setting up a database which uses random UUIDs for primary keys, I have:

CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT);
CREATE TABLE beta (aref UUID REFERENCES alpha (id));
INSERT INTO alpha (value) VALUES (42);

Then over a foreign key, to have that record in "alpha" be the default target for insertions into the "beta" table table:

DO $$
 DECLARE l_id UUID;
 BEGIN
 SELECT alpha.id FROM alpha INTO l_id;
 ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id;
 END;
$$ LANGUAGE plpgsql;

ERROR: column "l_id" does not exist
CONTEXT: SQL statement "ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id"
PL/pgSQL function inline_code_block line 1 at SQL statement

How do I use the value of a variable as the default value for a column?

• András Váczi: Does this all mean that your default keeps changing? If so, I would not set the default but use a subquery in the insertion statement to obtain the value.
• Mark K Cowan: The value won't change, but is not known when writing the script. I could probably hardcode a pre-generated UUID instead of relying on the automatically generated one, but I'm wondering if there is a solution to this problem for future reference.
• Abelisto: execute format('ALTER TABLE beta ALTER COLUMN aref SET DEFAULT %L::uuid', l_id);
• user1822: @Abelisto: you should add that as an answer
• Mark K Cowan: I was hoping for something cleaner than an eval-style answer, but I'm happy to accept that as the answer though if you post it as such.

Answer 1

You seem to assume variable substitution for an SQL utility command like ALTER TABLE, but that's not implemented.

Also not possible to pass values for utility commands to EXECUTE with the USING clause in plpgsql.
Detailed explanation for both in this related answer on SO:

• Error when setting n_distinct using a plpgsql variable

Concatenate the statement, then execute it - like @Abelisto hinted. But you don't need a variable, you can concatenate the lookup value directly:

DO $$
BEGIN
EXECUTE format('ALTER TABLE beta ALTER COLUMN aref SET DEFAULT %L'
 , (SELECT a.id::text FROM alpha a));
END $$;

The SELECT must return a single value.
I cast to text explicitly, which is not strictly necessary. Casting the string literal to the target type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

• CREATE SEQUENCE using expressions with psql variables for parameters