3

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?

  • 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. – dezso Jul 25 '16 at 12:36
  • 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. – Mark K Cowan Jul 25 '16 at 12:44
  • 3
    execute format('ALTER TABLE beta ALTER COLUMN aref SET DEFAULT %L::uuid', l_id); – Abelisto Jul 25 '16 at 13:38
  • @Abelisto: you should add that as an answer – a_horse_with_no_name Jul 25 '16 at 13:51
  • 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. – Mark K Cowan Jul 25 '16 at 13:52
3

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.