Re: Updatable Views - DEFAULT doesn't inherit from table???

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