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@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
	-- 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@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?   |