Re: Problem Using RowType Declaration with Table Domains

"George Weaver" <gweaver(at)shaw(dot)ca> writes:
> I have the following (very simplified) scenario:

> CREATE DOMAIN orderstatus AS text NOT NULL DEFAULT 'Open';

> CREATE TABLE orders ( orderno serial
> , status orderstatus
> , PRIMARY KEY (orderno));

> CREATE OR REPLACE FUNCTION getOrder(int4)
> RETURNS
> orders
> AS
> $$DECLARE
> orderno_in alias for $1;
> saleorder orders%rowtype;
> ...
> test1=# select * from getorder(3);
> ERROR: domain orderstatus does not allow null values
> CONTEXT: PL/pgSQL function "getorder" line 4 during statement block local
> variable initialization

> Is there a way around this?

I think you've just hit one of the many reasons why declaring domains
with NOT NULL constraints is a bad idea. If you are utterly wedded to
doing that, you can assign an initial value to the "saleorder" variable
that sets saleorder.status to a valid non-null value. But be prepared
for more pain in the future, and more pain after that. (Hint: what do
you expect a LEFT JOIN to the orders table to produce?) NOT NULL domain
constraints may perhaps not have been the SQL committee's worst idea
ever, but they're definitely in the top ten.

regards, tom lane