From: | "Matthew N(dot) Dodd" <winter(at)jurai(dot)net> |
---|---|
To: | Paul A Vixie <paul(at)vix(dot)com> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] CIDR/INET type and IANA/ICANN |
Date: | 1998-10-21 16:33:06 |
Message-ID: | Pine.BSF.4.02.9810211223380.17054-100000@sasami.jurai.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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? |
From | Date | Subject | |
---|---|---|---|
Next Message | D'Arcy J.M. Cain | 1998-10-21 16:33:14 | Re: [HACKERS] New INET and CIDR types |
Previous Message | Jackson, DeJuan | 1998-10-21 16:32:42 | RE: [HACKERS] Anyone object to simplifying INSTALL instructions? |