new.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS owner     CASCADE;
DROP TABLE IF EXISTS pet_breed CASCADE;
DROP TABLE IF EXISTS breed;
DROP TABLE IF EXISTS pet;

CREATE TABLE owner (
    id      SERIAL PRIMARY KEY,
    name    varchar(20),
    api_key uuid
);

CREATE TABLE pet (
    id       SERIAL PRIMARY KEY,
    name     varchar(20),
    age      int,
    owner_id int references owner(id)
);

CREATE TABLE breed (
    id   SERIAL PRIMARY KEY,
    name varchar(20)
);

CREATE TABLE pet_breed (
    pet_id   int references pet(id),
    breed_id int references breed(id),
    PRIMARY KEY(pet_id, breed_id)
);

INSERT INTO owner (name,    api_key           )
VALUES            ('bob',   uuid_generate_v4()),
                  ('frank', uuid_generate_v4())
;

INSERT INTO breed (name       )
VALUES            ('poodle'   ),
                  ('beagle'   ),
                  ('retriever')
;

INSERT INTO pet (name,     age, owner_id)
VALUES          ('rocko',    1,        1),
                ('fluffy',   6,        1),
                ('rex',      4,        1),
                ('fido',     3,        2),
                ('king',     7,        2)
;

INSERT INTO pet_breed (pet_id, breed_id)
VALUES                (     1,        2),
                      (     1,        3),
                      (     2,        3),
                      (     3,        1),
                      (     4,        3),
                      (     5,        1)
;

CREATE OR REPLACE VIEW v_owner AS
    SELECT   owner.*,
             array_agg(pet.id) AS pets
    FROM     owner
             INNER JOIN pet ON owner.id = pet.owner_id
    GROUP BY owner.id
;

CREATE OR REPLACE VIEW v_pet AS
    SELECT   owner.api_key,
             pet.*,
             array_agg(breed.id) AS breeds
    FROM                     owner
             INNER JOIN      pet       ON owner.id = pet.owner_id
             LEFT OUTER JOIN pet_breed ON pet.id   = pet_breed.pet_id
             LEFT OUTER JOIN breed     ON breed.id = pet_breed.breed_id
    GROUP BY owner.api_key,
             pet.id
;

CREATE OR REPLACE VIEW v_fk AS
select  att2.attname as "child_column",
        cl.relname   as "parent_table",
        att.attname  as "parent_column"
from    (
        select  unnest(con1.conkey)  as "parent",
                unnest(con1.confkey) as "child", 
                con1.confrelid,    
                con1.conrelid
        from         pg_class cl
                join pg_namespace ns    on cl.relnamespace = ns.oid
                join pg_constraint con1 on con1.conrelid = cl.oid
        where       cl.relname   = 'pet_breed'
                and ns.nspname   = 'public'
                and con1.contype = 'f'
        ) con
        join pg_attribute att  on att.attrelid = con.confrelid and att.attnum = con.child
        join pg_class cl       on cl.oid = con.confrelid
        join pg_attribute att2 on att2.attrelid = con.conrelid and att2.attnum = con.parent
;