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