OSSP CVS Repository

ossp-pkg/as/as-db/bres/bres-rule.sql

---

-- Create Database Schema
DROP TABLE ac CASCADE;
CREATE TABLE ac(
    ac_oid    INTEGER DEFAULT 100,
    ac_name   TEXT,
    ac_hid    INTEGER
);

DROP TABLE fo CASCADE;
CREATE TABLE fo(
    ac_oid    INTEGER DEFAULT 100,
    ac_name   TEXT,
    ac_hid    INTEGER
);

-- DROP RULE "_RETURN";
-- CREATE OR REPLACE RULE "_RETURN" AS
--     ON SELECT TO fo
--     DO INSTEAD
--         -- SELECT * FROM ac WHERE ac_hid = 1;
--         -- SELECT ac_oid,ac_name,max(ac_hid) AS ac_hid FROM ac GROUP BY ac_oid,ac_name;
--         ( SELECT ac.ac_oid,ac.ac_name,ac.ac_hid FROM ac, ( SELECT ac_oid,max(ac_hid) AS ac_hid
--            FROM ac GROUP BY ac_oid) AS result WHERE ac.ac_oid = result.ac_oid AND
--           ac.ac_hid = result.ac_hid );

CREATE OR REPLACE RULE "fuck" AS
    ON INSERT TO fo
    DO INSTEAD
        ( INSERT INTO ac VALUES (NEW.ac_oid, NEW.ac_name, NEW.ac_hid); );

CREATE OR REPLACE FUNCTION fo_func (INTEGER)
RETURNS SETOF fo LANGUAGE 'sql' AS '
    SELECT * FROM ac WHERE ac_hid = $1;
';

INSERT INTO ac VALUES (10, 'foo1', 1);
INSERT INTO ac VALUES (10, 'foo2', 2);
INSERT INTO ac VALUES (10, 'foo3', 3);
INSERT INTO ac VALUES (20, 'bar4', 1);
INSERT INTO ac VALUES (20, 'bar5', 2);
INSERT INTO ac VALUES (20, 'bar6', 3);

SELECT * FROM ac;
SELECT * FROM fo;
SELECT * FROM ac WHERE ac_oid = 10;
SELECT * FROM fo WHERE ac_oid = 10;

SELECT ac_name FROM (SELECT * FROM ac WHERE ac_hid = 1) AS foo WHERE ac_oid = 10;
SELECT ac_name FROM fo WHERE ac_oid = 10;
SELECT ac_name FROM fo_func(1) AS foo WHERE ac_oid = 10;

INSERT INTO fo (ac_name, ac_hid) VALUES ('quux1', 4);

-- Problem: fo requires same defaults for columns than ac!

---