Re: [HACKERS] CIDR/INET type and IANA/ICANN

On Tue, 20 Oct 1998, Paul A Vixie wrote:

CREATE FUNCTION check_primary_key ()RETURNS opaqueAS '/opt/PGpgsql/modules/refint.so'    -- replace with real
pathLANGUAGE'c'
 
;

CREATE FUNCTION check_foreign_key ()RETURNS opaqueAS '/opt/PGpgsql/modules/refint.so'    -- replace with real
pathLANGUAGE'c'
 
;

> CREATE TABLE countries (
>     code    CHAR(2)        NOT NULL,    -- US
>     name    VARCHAR(25)    NOT NULL,    -- United States
PRIMARY KEY (code)

> );
> CREATE UNIQUE INDEX country_codes ON countries ( code );
> INSERT INTO countries VALUES ('US', 'United States');
> INSERT INTO countries VALUES ('CA', 'Canada');
> INSERT INTO countries VALUES ('MX', 'Mexico');
> INSERT INTO countries VALUES ('UK', 'United Kingdom');
> INSERT INTO countries VALUES ('SE', 'Sweden');
> -- XXX more needed here
> GRANT all ON countries TO www;

[snip]

> DROP TABLE contacts;
> CREATE TABLE contacts (
>     handle    VARCHAR(16)    NOT NULL,    -- PV15
>     name    TEXT        NOT NULL,    -- Paul Vixie
>     email    VARCHAR(96)    NOT NULL,    -- paul@vix.com
>     pmail    TEXT        NOT NULL,    -- 950 Charter Street
>                         -- Redwood City, CA
>     pcode    VARCHAR(64)    NOT NULL,    -- 94062
>     country    CHAR(2)        NOT NULL,    -- US
>     phone1    VARCHAR(64)    NOT NULL,    -- +1.650.779.7001
>     phone2    VARCHAR(64)    ,
>     pgpkid    CHAR(8)        NOT NULL,    -- 8972C7C1
>     ntype    CHAR(1)        NOT NULL,    -- notify A
>     comment    TEXT        ,
>     www    VARCHAR(96)    ,        -- http://www.vix.com/
>     format    FLOAT        ,        -- 1.0
>     created    DATETIME    NOT NULL,
>     updated    DATETIME    NOT NULL,
-- note: while postgresql does not support 'foreign key'-- statements, we include it here just to remind ourselves--
thatwe are using the refint.so calls to do the same thing.FOREIGN KEY (country) REFERENCES countries    ON DELETE
CASCADE   ON UPDATE CASCADE
 
> );
> CREATE UNIQUE INDEX contact_handles ON contacts ( handle );
> -- wish there was a way to require country to match a key in countries.
> GRANT all ON contacts TO www;

CREATE TRIGGER t_countries_countryBEFORE DELETE OR UPDATE ON countriesFOR EACH ROWEXECUTE PROCEDUREcheck_foreign_key(1,
'cascade','code',    'contacts', 'countries');
 

CREATE TRIGGER t_contacts_countriesBEFORE INSERT OR UPDATE ON contactsFOR EACH ROWEXECUTE
PROCEDUREcheck_primary_key('countries','countries', 'code');
 

[snip]

Look in contrib for the refint.c file and examples.

-- 
| Matthew N. Dodd  | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |      This Space For Rent     | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?   |