0010204: Problem with views and rules in postgresql

Description

Can't create a view with a rule that inserts a row in the database and make it work without the "Database Error" message.

For instance if you want to insert in different tables, you can use a view with some tables of the database and a rule when insert that calcules the needed data for each table and after it inserts the data in the tables, it work as expected but when it finnish no matter if it inserted or not it always shows the error "Database Error".

The expected solution should be something different if it is a view.

Steps To Reproduce

--create table m_simple1
CREATE TABLE m_simple1 (
    m_simple1_id character varying(32) NOT NULL PRIMARY KEY,
    ad_client_id character varying(32) NOT NULL REFERENCES ad_client (ad_client_id) ON DELETE CASCADE ON UPDATE CASCADE,
    ad_org_id character varying(32) NOT NULL REFERENCES ad_org (ad_org_id) ON DELETE CASCADE ON UPDATE CASCADE,
    isactive character(1) DEFAULT 'Y'::bpchar NOT NULL CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
    created timestamp without time zone DEFAULT now() NOT NULL,
    createdby character varying(32) NOT NULL,
    updated timestamp without time zone DEFAULT now() NOT NULL,
    updatedby character varying(32) NOT NULL,
    inivalue numeric(20)
) WITHOUT OIDS;;

--create table m_simple2
CREATE TABLE m_simple2 (
    m_simple2_id character varying(32) NOT NULL PRIMARY KEY,
    ad_client_id character varying(32) NOT NULL REFERENCES ad_client (ad_client_id) ON DELETE CASCADE ON UPDATE CASCADE,
    ad_org_id character varying(32) NOT NULL REFERENCES ad_org (ad_org_id) ON DELETE CASCADE ON UPDATE CASCADE,
    isactive character(1) DEFAULT 'Y'::bpchar NOT NULL CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
    created timestamp without time zone DEFAULT now() NOT NULL,
    createdby character varying(32) NOT NULL,
    updated timestamp without time zone DEFAULT now() NOT NULL,
    updatedby character varying(32) NOT NULL,
    endvalue numeric(20),
    m_simple1_id character varying(32) NOT NULL REFERENCES m_simple1 (m_simple1_id)
) WITHOUT OIDS;;

--Create view for the tables
CREATE OR REPLACE VIEW m_simple_v
    AS
    (
    SELECT s1.m_simple1_id as m_simple_v_id,'0' as ad_client_id,'0' as ad_org_id ,'Y' as isactive ,now() as created ,
        '0' as createdby, now() as updated ,'0' as updatedby,s1.inivalue as iniv,s2.endvalue as endv
    FROM m_simple2 s2 left join m_simple1 s1 on s2.m_simple1_id=s1.m_simple1_id
    );

CREATE OR REPLACE FUNCTION m_simple_v_ins_f(p_s m_simple_v )
RETURNS character varying(32)
AS $$
DECLARE
v_s1_id character varying(32):=get_uuid();
v_s2_id character varying(32):=get_uuid();
BEGIN

INSERT INTO m_simple1 (m_simple1_id, ad_client_id,ad_org_id,isactive,created,createdby,updated,updatedby,inivalue)
                    VALUES( v_s1_id,p_s.ad_client_id,p_s.ad_org_id,'Y',now(),p_s.createdby,now(),p_s.updatedby,
                    p_s.iniv);
INSERT INTO m_simple2 (m_simple2_id, ad_client_id,ad_org_id,isactive,created,createdby,updated,updatedby,endvalue,m_simple1_id)
                    VALUES( v_s2_id,p_s.ad_client_id,p_s.ad_org_id,'Y',now(),p_s.createdby,now(),p_s.updatedby,
                    p_s.endv,v_s1_id);
    return p_s.m_Simple_v_id;
  END; --BODY
$$ LANGUAGE plpgsql;


    --Now create a rule when insert
CREATE OR REPLACE RULE "m_simple_v_ins" AS
    ON INSERT TO m_simple_v
    DO INSTEAD
(
select m_simple_v_ins_f(NEW) as m_simple_v_id;
);

--Do
Insert into m_simple_v (m_simple_v_id, ad_client_id,ad_org_id,isactive,created,createdby,updated,updatedby,iniv,endv)
    values (get_uuid(),'0','0','Y',now(),'0',now(),'0',0,10);
/*
Check the database in table m_simple 1 we have the data inserted and in m_simple 2 too. No erros has thrown.
*/

/*Now go to the Aplication dictionary, create on it the database tables and the view as view, a window and a tab, for the view and select the view.

Compile, deploy
Wait

Try now to insert into the view something, it works, but an error is thrown "database error".

Also Export.database doesn't export rules views. at least in the modules (not sure in core level if it does)
*/

Proposed Solution

When a table is marked as a view it should check if the rule returns a valid id from the view table. If it does then it should not say database error and instead show a Green message saying all ok.

Notes
(0019325) rafaroda (developer) 2009-08-31 11:32	Antonio, Can you please comment on this issue? Thanks.

(0044682) marvintm (developer) 2012-01-30 10:36	Views with rules are currently not supported in dbsm. There is a feature request for this: https://issues.openbravo.com/view.php?id=15151 [^]

Issue History
Date Modified	Username	Field	Change
2009-08-06 13:06	joan	New Issue
2009-08-06 13:06	joan	Assigned To	=> rafaroda
2009-08-31 11:32	rafaroda	Note Added: 0019325
2009-08-31 11:32	rafaroda	Assigned To	rafaroda => marvintm
2009-08-31 11:32	rafaroda	Status	new => acknowledged
2009-08-31 11:32	rafaroda	Category	00. Application dictionary => A. Platform
2012-01-30 10:35	marvintm	Status	acknowledged => scheduled
2012-01-30 10:35	marvintm	fix_in_branch	=> pi
2012-01-30 10:36	marvintm	Relationship added	duplicate of 0015151
2012-01-30 10:36	marvintm	Note Added: 0044682
2012-01-30 10:36	marvintm	Status	scheduled => closed
2012-01-30 10:36	marvintm	Resolution	open => duplicate