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

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

CREATE FUNCTION check_primary_key ()
RETURNS opaque
AS '/opt/PGpgsql/modules/refint.so' -- replace with real path
LANGUAGE 'c'
;

CREATE FUNCTION check_foreign_key ()
RETURNS opaque
AS '/opt/PGpgsql/modules/refint.so' -- replace with real path
LANGUAGE '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(at)vix(dot)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
-- that we 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_country
BEFORE DELETE OR UPDATE ON countries
FOR EACH ROW
EXECUTE PROCEDURE
check_foreign_key(1, 'cascade', 'code',
'contacts', 'countries');

CREATE TRIGGER t_contacts_countries
BEFORE INSERT OR UPDATE ON contacts
FOR EACH ROW
EXECUTE PROCEDURE
check_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(at)jurai(dot)net | This Space For Rent | ix86,sparc,m68k,pmax,vax |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage? |