On Tue, Jun 22, 2010 at 12:26 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "George Weaver" <gweaver@xxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
