| 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: | Whole Thread | Raw Message | 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
| 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 |