Already changed source_name keys in deploy_depends_on to use the new

[dbsrgits/DBIx-Class-ResultSource-MultipleTableInheritance.git] / t / sql / Cafe-0.1-PostgreSQL.sql

-- 
-- Created by SQL::Translator::Producer::PostgreSQL
-- Created on Sun Jul 11 20:05:37 2010
-- 
--
-- Table: _coffee
--
DROP TABLE "_coffee" CASCADE;
CREATE TABLE "_coffee" (
  "id" serial NOT NULL,
  "flavor" text DEFAULT 'good' NOT NULL,
  PRIMARY KEY ("id")
);

--
-- Table: _sumatra
--
DROP TABLE "_sumatra" CASCADE;
CREATE TABLE "_sumatra" (
  "id" integer NOT NULL,
  "aroma" text NOT NULL,
  PRIMARY KEY ("id")
);

--
-- Table: sugar
--
DROP TABLE "sugar" CASCADE;
CREATE TABLE "sugar" (
  "id" serial NOT NULL,
  "sweetness" integer NOT NULL,
  PRIMARY KEY ("id")
);

--
-- View: "coffee"
--
DROP VIEW IF EXISTS "coffee";
CREATE VIEW "coffee" ( "id", "flavor" ) AS
    SELECT _coffee.id, flavor FROM _coffee;

CREATE OR REPLACE FUNCTION coffee_insert
  (_flavor TEXT)
  RETURNS VOID AS $function$
  BEGIN
    INSERT INTO _coffee ( flavor) VALUES ( _flavor );
  END;
$function$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION coffee_update
  (_id INTEGER, _flavor TEXT)
  RETURNS VOID AS $function$
  BEGIN
    UPDATE _coffee SET flavor = _flavor WHERE ( id = _id );
  END;
$function$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION coffee_delete
  (_id INTEGER)
  RETURNS VOID AS $function$
  BEGIN
    DELETE FROM _coffee WHERE ( id = _id );
  END;
$function$ LANGUAGE plpgsql;


CREATE RULE _coffee_insert_rule AS
  ON INSERT TO coffee
  DO INSTEAD (
    SELECT coffee_insert(NEW.flavor)
  );


CREATE RULE _coffee_update_rule AS
  ON UPDATE TO coffee
  DO INSTEAD (
    SELECT coffee_update(OLD.id, NEW.flavor)
  );


CREATE RULE _coffee_delete_rule AS
  ON DELETE TO coffee
  DO INSTEAD (
    SELECT coffee_delete(OLD.id)
  );

;

--
-- View: "sumatra"
--
DROP VIEW IF EXISTS "sumatra";
CREATE VIEW "sumatra" ( "id", "flavor", "sweetness", "aroma" ) AS
    SELECT _sumatra.id, aroma, sweetness, flavor FROM _sumatra _sumatra JOIN sugar sugar ON sugar.id = _sumatra.id JOIN coffee coffee ON coffee.id = _sumatra.id;

CREATE OR REPLACE FUNCTION sumatra_insert
  (_aroma TEXT, _sweetness INTEGER, _flavor TEXT)
  RETURNS VOID AS $function$
  BEGIN
    INSERT INTO coffee ( flavor) VALUES ( _flavor );
    INSERT INTO _sumatra ( aroma, id) VALUES ( _aroma, currval('_coffee_id_seq') );
    INSERT INTO sugar ( id, sweetness) VALUES ( currval('_coffee_id_seq'), _sweetness );
  END;
$function$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sumatra_update
  (_id INTEGER, _aroma TEXT, _sweetness INTEGER, _flavor TEXT)
  RETURNS VOID AS $function$
  BEGIN
    UPDATE _sumatra SET aroma = _aroma WHERE ( id = _id );
    UPDATE sugar SET sweetness = _sweetness WHERE ( id = _id );
    UPDATE coffee SET flavor = _flavor WHERE ( id = _id );
  END;
$function$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sumatra_delete
  (_id INTEGER)
  RETURNS VOID AS $function$
  BEGIN
    DELETE FROM _sumatra WHERE ( id = _id );
    DELETE FROM sugar WHERE ( id = _id );
    DELETE FROM coffee WHERE ( id = _id );
  END;
$function$ LANGUAGE plpgsql;


CREATE RULE _sumatra_insert_rule AS
  ON INSERT TO sumatra
  DO INSTEAD (
    SELECT sumatra_insert(NEW.aroma, NEW.sweetness, NEW.flavor)
  );


CREATE RULE _sumatra_update_rule AS
  ON UPDATE TO sumatra
  DO INSTEAD (
    SELECT sumatra_update(OLD.id, NEW.aroma, NEW.sweetness, NEW.flavor)
  );


CREATE RULE _sumatra_delete_rule AS
  ON DELETE TO sumatra
  DO INSTEAD (
    SELECT sumatra_delete(OLD.id)
  );

;