Обсуждение: Updatable Views - DEFAULT doesn't inherit from table???
Hi,
I have problems with inserting rows into an updatable view through it's
insert rule.
Running this:
insert into view_products_1
(id,firmid,name_en,name_hu,artnum1,artnum2,description_hu,description_en,pkgunitid,minpkg,customstariff,vat)
values
('23','1','dddddddd','dddddddddddd','dddddddddd','dddddddd','ddddddddd','dddddddddd','1','10','2022220','20')
the engine sends this error:
ERROR: null value in column "qtyunitid" violates not-null constraint
********** Error **********
ERROR: null value in column "qtyunitid" violates not-null constraint
SQL state: 23502
But in the table definition I defined DEFULT=(-1) for this field. What's
going wrong? Shouldn't it inherit these settings from the table?
Many thanks,
--
Best Regards,
Csaba Együd
IN-FO Studio
Here is the table:
-------------------------------------------------------------------------------------------
CREATE TABLE whm.products
(
id serial NOT NULL,
firmid integer NOT NULL,
name_en character varying(250) NOT NULL DEFAULT ''::character varying,
name_hu character varying(250) NOT NULL DEFAULT ''::character varying,
artnum1 character varying(250) NOT NULL,
artnum2 character varying(250) NOT NULL DEFAULT ''::character varying,
description_hu character varying(512) NOT NULL DEFAULT ''::character
varying,
createtime timestamp with time zone NOT NULL DEFAULT now(),
"createuser" name NOT NULL DEFAULT "session_user"(),
lastmodtime timestamp with time zone NOT NULL DEFAULT now(),
lastmoduser name NOT NULL DEFAULT "session_user"(),
description_en character varying(512) NOT NULL DEFAULT ''::character
varying,
qtyunitid integer NOT NULL DEFAULT (-1),
pkgunitid integer NOT NULL DEFAULT (-1),
minpkg integer NOT NULL DEFAULT 0,
customstariff character varying(64) NOT NULL DEFAULT ''::character
varying,
vat numeric NOT NULL DEFAULT 20,
service boolean NOT NULL DEFAULT false,
notes character varying(512) DEFAULT ''::character varying,
CONSTRAINT pk_products_id PRIMARY KEY (id),
CONSTRAINT fk_products_firmid FOREIGN KEY (firmid) REFERENCES whm.firms
(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES
whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT products_pkgunitid FOREIGN KEY (pkgunitid) REFERENCES
whm.pkgunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
And here is the definition of the view:
----------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW whm.view_products_1 AS
SELECT products.id, products.firmid, products.name_en, products.name_hu,
products.artnum1, products.artnum2, products.description_hu,
products.createtime, products.createuser, products.lastmodtime,
products.lastmoduser, products.description_en, products.qtyunitid,
products.pkgunitid, products.minpkg, products.customstariff, products.vat,
products.service, products.notes FROM whm.products WHERE products.firmid =
1;
CREATE OR REPLACE RULE view_products_1_insert AS
ON INSERT TO whm.view_products_1 DO INSTEAD INSERT INTO whm.products
(firmid, name_en, name_hu, artnum1, artnum2, description_hu, description_en,
qtyunitid, pkgunitid, minpkg, customstariff, vat, service, notes)
VALUES (1, new.name_en, new.name_hu, new.artnum1, new.artnum2,
new.description_hu, new.description_en, new.qtyunitid, new.pkgunitid,
new.minpkg, new.customstariff, new.vat, new.service, new.notes);
maybe that constraint ?? CONSTRAINT fk_products_qtyunitid FOREIGN KEY
(qtyunitid) REFERENCES whm.qtyunits (id) MATCH FULL ON UPDATE
CASCADE ON DELETE CASCADE,
Also, that table seem to be far away from perfect, too many fields,
you should chop it into few smaller tables.
2008/12/5 Csaba Együd <csegyud@gmail.com>:
> Hi,
> I have problems with inserting rows into an updatable view through it's
> insert rule.
> Running this:
> insert into view_products_1
> (id,firmid,name_en,name_hu,artnum1,artnum2,description_hu,description_en,pkgunitid,minpkg,customstariff,vat)
> values
> ('23','1','dddddddd','dddddddddddd','dddddddddd','dddddddd','ddddddddd','dddddddddd','1','10','2022220','20')
>
> the engine sends this error:
> ERROR: null value in column "qtyunitid" violates not-null constraint
>
> ********** Error **********
> ERROR: null value in column "qtyunitid" violates not-null constraint
> SQL state: 23502
>
> But in the table definition I defined DEFULT=(-1) for this field. What's
> going wrong? Shouldn't it inherit these settings from the table?
>
> Many thanks,
>
> --
> Best Regards,
> Csaba Együd
> IN-FO Studio
>
>
> Here is the table:
> -------------------------------------------------------------------------------------------
> CREATE TABLE whm.products
> (
> id serial NOT NULL,
> firmid integer NOT NULL,
> name_en character varying(250) NOT NULL DEFAULT ''::character varying,
> name_hu character varying(250) NOT NULL DEFAULT ''::character varying,
> artnum1 character varying(250) NOT NULL,
> artnum2 character varying(250) NOT NULL DEFAULT ''::character varying,
> description_hu character varying(512) NOT NULL DEFAULT ''::character
> varying,
> createtime timestamp with time zone NOT NULL DEFAULT now(),
> "createuser" name NOT NULL DEFAULT "session_user"(),
> lastmodtime timestamp with time zone NOT NULL DEFAULT now(),
> lastmoduser name NOT NULL DEFAULT "session_user"(),
> description_en character varying(512) NOT NULL DEFAULT ''::character
> varying,
> qtyunitid integer NOT NULL DEFAULT (-1),
> pkgunitid integer NOT NULL DEFAULT (-1),
> minpkg integer NOT NULL DEFAULT 0,
> customstariff character varying(64) NOT NULL DEFAULT ''::character varying,
> vat numeric NOT NULL DEFAULT 20,
> service boolean NOT NULL DEFAULT false,
> notes character varying(512) DEFAULT ''::character varying,
> CONSTRAINT pk_products_id PRIMARY KEY (id),
> CONSTRAINT fk_products_firmid FOREIGN KEY (firmid) REFERENCES whm.firms
> (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
> CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES
> whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
> CONSTRAINT products_pkgunitid FOREIGN KEY (pkgunitid) REFERENCES
> whm.pkgunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
> )
> WITH (OIDS=FALSE);
>
>
>
> And here is the definition of the view:
> ----------------------------------------------------------------------------------------------------------------
> CREATE OR REPLACE VIEW whm.view_products_1 AS
> SELECT products.id, products.firmid, products.name_en, products.name_hu,
> products.artnum1, products.artnum2, products.description_hu,
> products.createtime, products.createuser, products.lastmodtime,
> products.lastmoduser, products.description_en, products.qtyunitid,
> products.pkgunitid, products.minpkg, products.customstariff, products.vat,
> products.service, products.notes FROM whm.products WHERE products.firmid =
> 1;
>
> CREATE OR REPLACE RULE view_products_1_insert AS
> ON INSERT TO whm.view_products_1 DO INSTEAD INSERT INTO whm.products
> (firmid, name_en, name_hu, artnum1, artnum2, description_hu, description_en,
> qtyunitid, pkgunitid, minpkg, customstariff, vat, service, notes)
> VALUES (1, new.name_en, new.name_hu, new.artnum1, new.artnum2,
> new.description_hu, new.description_en, new.qtyunitid, new.pkgunitid,
> new.minpkg, new.customstariff, new.vat, new.service, new.notes);
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
GJ
Csaba Együd wrote: > Hi, > I have problems with inserting rows into an updatable view through it's > insert rule. [snip] > But in the table definition I defined DEFULT=(-1) for this field. What's > going wrong? Shouldn't it inherit these settings from the table? Maybe, but it doesn't (and I think I've seen someone arguing it shouldn't). You can manually apply constraints/defaults etc. to the view though. I can't recall if you do ALTER VIEW view_products_1 or ALTER TABLE view_products_1, but it's one of them. -- Richard Huxton Archonet Ltd
"Richard Huxton" <dev@archonet.com> a következőket írta üzenetében news:49393224.7080301@archonet.com... Csaba Együd wrote: > Hi, > I have problems with inserting rows into an updatable view through it's > insert rule. [snip] > But in the table definition I defined DEFULT=(-1) for this field. What's > going wrong? Shouldn't it inherit these settings from the table? Maybe, but it doesn't (and I think I've seen someone arguing it shouldn't). You can manually apply constraints/defaults etc. to the view though. I can't recall if you do ALTER VIEW view_products_1 or ALTER TABLE view_products_1, but it's one of them. -- Richard Huxton Archonet Ltd Richard, Thx for your reply. Is there any possible way to generate an sql to "copy" these defaults to the view. --Csaba
""Grzegorz Jaśkiewicz"" <gryzman@gmail.com> a következőket írta üzenetében news:2f4958ff0812050549p6b86bc1dqc81c9bc726617198@mail.gmail.com... > maybe that constraint ?? CONSTRAINT fk_products_qtyunitid FOREIGN KEY > (qtyunitid) REFERENCES whm.qtyunits (id) MATCH FULL ON UPDATE > CASCADE ON DELETE CASCADE, > > Also, that table seem to be far away from perfect, too many fields, > you should chop it into few smaller tables. > > > 2008/12/5 Csaba Együd <csegyud@gmail.com>: Hi, thx for your reply too. Not that I guess because there is a default row in qtyunits with id=-1. Too many fields: How would you chop this table? thx, -- Csaba
Csaba Együd wrote: > Thx for your reply. Is there any possible way to generate an sql to > "copy" these defaults to the view. Nothing pre-packaged that I know of. You could probably do something copying values about in pg_attribute and pg_constraint, but that'd be an "at your own risk" sort of activity I suspect. For simpler defaults etc. you could probably get what you need from information_schema.columns -- Richard Huxton Archonet Ltd
>> 2008/12/5 Csaba Együd <csegyud@gmail.com>: > > Hi, thx for your reply too. Not that I guess because there is a default row > in qtyunits with id=-1. > > Too many fields: How would you chop this table? I would generally try to normalize it. Queries are going to be bit more complicated than, but it would be easier to manage it, and extend. -- GJ
"Richard Huxton" <dev@archonet.com> a következőket írta üzenetében news:49393A81.3030702@archonet.com... Csaba Együd wrote: > Thx for your reply. Is there any possible way to generate an sql to > "copy" these defaults to the view. Nothing pre-packaged that I know of. You could probably do something copying values about in pg_attribute and pg_constraint, but that'd be an "at your own risk" sort of activity I suspect. For simpler defaults etc. you could probably get what you need from information_schema.columns -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Or use of coalesce() function??? I mean sg like: ..., qtyunitid = coalesce(NEW.qtyunitid, -1), ... in the Rule def. -- Csaba
2008/12/5 Richard Huxton <dev@archonet.com>: > I can't recall if you do ALTER VIEW view_products_1 or ALTER > TABLE view_products_1, but it's one of them. It seems odd, but adding defaults to a VIEW is done with ALTER TABLE. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug