Thread: Problem Using RowType Declaration with Table Domains

Hi all,

I have the following (very simplified) scenario:

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

ALTER DOMAIN orderstatus ADD CONSTRAINT orderstatus_valid
                CHECK (VALUE IN ('Open', 'Shipped', Cancelled'));

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

INSERT INTO orders (status) VALUES('Open'),('Open'),('Shipped');

CREATE OR REPLACE FUNCTION getOrder(int4)
RETURNS
orders
AS
$$DECLARE
orderno_in alias for $1;
saleorder orders%rowtype;
BEGIN
SELECT INTO saleorder * FROM orders WHERE orderno = orderno_in;
RETURN saleorder;
END;
$$
VOLATILE
LANGUAGE 'plpgsql';

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?

Thanks,
George

"George Weaver" <gweaver@shaw.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

On Tue, Jun 22, 2010 at 12:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "George Weaver" <gweaver@shaw.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.

I've been puzzling over this in the back of my mind all day...I have
some thoughts on this that I'd like to present:

why are domain constraints treated differently than regular column
constraints?  Constraint checking on rowtypes ISTM is a desirable
feature, or at least worth considering from the point of view of
future-proofing.  The sql misfeature that is REALLY problematic for
pl/pgsql authors is that you are not supposed to distinguish between
null::foo and (null, null)::foo.   I realize that domains have special
rules attached with casting but isn't this a general problem with
rowtypes and constraints?

I'm thinking that null::foo is what pl/pgsql variable initialization
ought to be doing w/o default supplied and shouldn't do any column
initialization (thereby invoking domain casting/constraint checking)
whatsoever.  (null, null)::foo should fail if the types have non null
constraints, etc.  IOW, I'm proposing to break with the sql standard
in terms of 'rowtype is null'.  This also means breaking with the
standard in terms of null::rowtypes...things should work as coalesce()
does in pg, not 'is null'.

pl/pgsql painted itself in a bit of a corner here.  Suppose we want to
enforce constraints on row types during casting. We must either:

a) accept that rowtypes will never honor table derived check/column
constraints in variable declaration because most tables have p keys
(not null) and enforcing constraints arbitrarily will break a large
percentage of existing code, or b) do two things, so that rowtype
constraints can be sneaked into pl/pgsql:

1) break with the sql standard implementing rowtype::null separately
from (null, null)::rowtype  (no great loss to the world IMNSHO)
2) make pl/pgsql composite initialization use rowtype::null, and
loudly advertise the distinction

so that:
DECLARE
  a some_row_type; -- will work, regardless of the column constraints
on the type
  b some_row_type default (null, null); -- will fail if fields are not null

That would break very little code, especially given that relying on
rowtype is null is asking for trouble given the current state of
affairs (it bears repeating: is null and coalesce give opposite
results).  The *only* case where variable declaration should fail w/o
supplied default is a domain w/not null constraint (as you say, this
don't use domains/not null if you're worried about this).

merlin