how to fix domain tebool does not allow null values in plpgsql function

Question

I'm looging for a way to use tebool type records in plpgsql mathod starting from Postgres 9.1

I tried

 CREATE DOMAIN tebool AS bool DEFAULT false NOT NULL;
create temp table test  ( test tebool ) on commit drop ;

CREATE OR REPLACE FUNCTION test()
   RETURNS numeric AS $$

DECLARE
r_test test;
begin

return 0;
end;  $$ language plpgsql;

select test();

but got error

ERROR:  domain tebool does not allow null values
CONTEXT:  PL/pgSQL function test() line 5 during statement block local variable initialization
********** Error **********

ERROR: domain tebool does not allow null values
SQL state: 23502
Context: PL/pgSQL function test() line 5 during statement block local variable initialization

How to fix this so that such record variable can created ?

Domain tebool default value is false so plpgsql must assing false to it and should not throw error.

wildplasser wildplasser · Accepted Answer · 2015-08-22T16:18:16

It appears that a default value for a domain type is not used as an initializer (in plpgsql) Obvious workaround: initialize it explicitely (to either true or false):

CREATE DOMAIN tebool AS bool DEFAULT false NOT NULL;
CREATE temp table ztest  ( ztest tebool ) on commit drop ;

CREATE OR REPLACE FUNCTION ztest()
   RETURNS numeric AS $$

DECLARE
r_test tebool = False;
BEGIN

RETURN 0;
END;  $$ language plpgsql;

SELECT ztest();

how to fix domain tebool does not allow null values in plpgsql function

1 Answers