Problem Using RowType Declaration with Table Domains

From: "George Weaver" <gweaver(at)shaw(dot)ca>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Problem Using RowType Declaration with Table Domains
Date: 2010-06-22 03:02:27
Message-ID: 6F3A80F719B0427B82D68AA9660D2B38@d800
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2010-06-22 03:08:32 Re: High Availability with Postgres
Previous Message Greg Smith 2010-06-22 02:36:32 Re: Unable to determine what has a particular OID