Re: [GENERAL] Tr: DUMP database for sample - Mailing list pgsql-general

DROP TABLE header;
CREATE TABLE header (
        year                    INTEGER NOT NULL,
        number                  INTEGER  NOT NULL,
        date                    DATE     NOT NULL,
        cod_client              CHAR(4)  NOT NULL,
        CONSTRAINT k_header PRIMARY KEY (year,number)
        );

DROP TABLE detail;
CREATE TABLE detail (
        year                    INTEGER NOT NULL,
        number                  INTEGER  NOT NULL,
        cod_product             CHAR(05) NOT NULL,
        qty                     INTEGER  NOT NULL,
        cost                    DECIMAL(8,2),
        CONSTRAINT k_detail PRIMARY KEY (year,number,cod_product),
        CONSTRAINT k_extern FOREIGN KEY(year,number) references HEADER
        );

drop function f_not_add_detail();
create function f_not_add_detail() returns opaque as '
declare
        /* to avoid insert detail if header doesn''t exist */
        tot int;
begin
        select number into tot from header
        where year = new.year and number = new.number;
        if not found then
                raise notice ''I cannot add a detail without header!'';
                return NULL;
        else
                return new;
        end if;
end;
' language 'plpgsql';

drop function f_del_detail();
create function f_del_detail() returns opaque as '
begin
        -- Delete details in cascade...
        delete from detail where
        detail.year = old.year
        and detail.number = old.number;
        return new;
end;
' language 'plpgsql';

drop function f_upd_detail();
create function f_upd_detail() returns opaque as '
begin
        -- Updates details keys in cascade...
        update detail set year = new.year, number = new.number
        where detail.year = old.year
        and detail.number = old.number;
        return new;
end;
' language 'plpgsql';
 

create trigger t_ins_after before INSERT
    on detail for each row execute procedure f_not_add_detail();

create trigger t_del_after after DELETE
    on detail for each row execute procedure f_not_add_detail();

create trigger t_del_after after DELETE
    on header for each row execute procedure f_del_detail();

create trigger t_upd_after after UPDATE
    on header for each row execute procedure f_upd_detail();

insert into header values(1999,321,current_date,'C128');
insert into detail values(1999,321,'B139',2,200.35);
insert into header values(1997,132,current_date,'C500');
insert into detail values(1997,132,'B166',3,120.50);
select * from header;
select * from detail;
update header set year=1998 where year=1999;
select * from header;
select * from detail;

delete from header where year=1998;
select * from header;
select * from detail;

insert into detail values(1999,321,'B139',2,200.35);

--results:
-----------------------------
insert into header values(1999,321,current_date,'C128');
INSERT 365317 1
insert into detail values(1999,321,'B139',2,200.35);
INSERT 365318 1
insert into header values(1997,132,current_date,'C500');
INSERT 365319 1
insert into detail values(1997,132,'B166',3,120.50);
INSERT 365320 1
select * from header;
year|number|date      |cod_client
----+------+----------+----------
1999|   321|10/08/1999|C128
1997|   132|10/08/1999|C500
(2 rows)

select * from detail;
year|number|cod_product|qty|  cost
----+------+-----------+---+------
1999|   321|B139       |  2|200.35
1997|   132|B166       |  3|120.50
(2 rows)

update header set year=1998 where year=1999;
UPDATE 1
select * from header;
year|number|date      |cod_client
----+------+----------+----------
1997|   132|10/08/1999|C500
1998|   321|10/08/1999|C128
(2 rows)

select * from detail;
year|number|cod_product|qty|  cost
----+------+-----------+---+------
1997|   132|B166       |  3|120.50
1998|   321|B139       |  2|200.35
(2 rows)
 

delete from header where year=1998;
DELETE 1
select * from header;
year|number|date      |cod_client
----+------+----------+----------
1997|   132|10/08/1999|C500
(1 row)

select * from detail;
year|number|cod_product|qty|  cost
----+------+-----------+---+------
1997|   132|B166       |  3|120.50
(1 row)
 

insert into detail values(1999,321,'B139',2,200.35);
NOTICE:  I cannot add a detail without header!
INSERT 0 0
 

José

Stéphane FILLON ha scritto:

-----Message d'origine-----
De : Stéphane FILLON <fillons@offratel.nc>
À : pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>;
pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Date : dimanche 1 août 1999 09:13
Objet : Tr: DUMP database for sample

>
>
>>Hi !
>>
>>I am trying a big commercial database application with PostgreSQL and I
>>encounter some problems seach as how to create a foreign key constraint..
>>
>>I would be very nice if someone could send me a dump of a real database
>>application with TRIGGER / FUNCTION / TYPE / FOREIGN KEY AND PRIMARY KEY.
>>
>>I think that it would be nice if we could make a documentation with a real
>>example. If you are interested I can make it during my build.
>>
>>
>>Best Regards,
>>
>>A special thanks for the PostgreSQL's team,, KEEP THE GOOD JOB.
>>
>>Stephane.
>>
>