0010204: Problem with views and rules in postgresql

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0010204

Openbravo ERP

A. Platform

public

2009-08-06 13:06

2012-01-30 10:36

Reporter

joan

Assigned To

marvintm

Priority

normal

Severity

major

Reproducibility

always

Status

closed

Resolution

duplicate

Platform

OS Version

Debian Etch

Product Version

2.50MP3

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

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.

Additional Information