How to declare variable having the type of a user-defined domain with a non-null check constraint

Question

I have defined a domain derived from a composite type that has a CHECK constraint to prevent one of its component values from ever being null. I want to write a plpgsql-language function that has a variable of this type. I am failing to declare such a variable.

Here is a simplified example of a scenario that gives me the same error message:

CREATE DOMAIN myint AS integer
  CONSTRAINT not_null CHECK (VALUE IS NOT NULL);

CREATE FUNCTION myfunc() RETURNS myint LANGUAGE plpgsql AS $$
  DECLARE
    notnullint myint;
  BEGIN
    notnullint := 1;
    RETURN notnullint;
  END
$$;

mydb=# select myfunc();
ERROR:  value for domain myint violates check constraint "not_null"
CONTEXT:  PL/pgSQL function myfunc() line 4 during statement 
block local variable initialization

Am I confused to be thinking it's impossible to bind a plpgsql variable to a value of a domain type that has a non null constraint? It's hard for me to believe PostgreSQL would have such a limitation.

Note: in my actual application I want to assign the variable the return value of an INSERT statement, so assigning the value within the DECLARE block seems inappropriate.

just note - maybe in PostgreSQL 12 there will be some optimization for domain types without constraint clause. When domain is used just like type alias. Now looks much more practical to don't use constraint when it is not really necessary. — Pavel Stehule

klin klin · Accepted Answer · 2018-12-06T01:13:42

You can initialize variables in the DECLARE section:

CREATE FUNCTION myfunc() RETURNS myint LANGUAGE plpgsql AS $$
  DECLARE
    notnullint myint = 0;
  BEGIN
    notnullint := 1;
    RETURN notnullint;
  END
$$;

Note: in my actual application I want to assign the variable the return value of an INSERT statement, so assigning the value within the DECLARE block seems inappropriate.

I do not understand this logic. In the case, the variable has to be initialized and you rather have no choice. Of course, you can assign another value to it in the function body.

How to declare variable having the type of a user-defined domain with a non-null check constraint

1 Answers