From: | Kovacs Zoltan Sandor <tip(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu> |
---|---|
To: | Moray McConnachie <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk> |
Cc: | Chris <chris(at)bitmead(dot)com>, James Macken <jmac(at)nemesis(dot)com(dot)au>, pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] How to set up a simple relational database |
Date: | 2000-02-16 13:00:02 |
Message-ID: | Pine.LNX.4.05.10002161340530.19905-100000@pc10.radnoti-szeged.sulinet.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> > create table staff (name text, packageid int);
> > create table package (id int, amount text);
> > SELECT * from staff, package where staff.packageid=package.id;
> But that omits the "establish the relationship part", by which I
> suspect James means "ensure foreign key integrity".
> I must I admit I would like to see a simple exposition of that (using
> the refint package, presumably) for this example - or is it not worth
> it if 7.0 is bringing native foreign key integrity checks?
Yes, right.
Until Jan and his developer friends are ready, I suggest you using this
small script (called "refint") for creating reference integrity; before
using it, you should enter this:
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
'/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler"
LANCOMPILER 'PL/pgSQL';
CREATE FUNCTION "check_primary_key" ( ) RETURNS opaque AS
'/usr/doc/postgresql-6.5.2/contrib/spi/refint.so' LANGUAGE 'C';
CREATE FUNCTION "check_foreign_key" ( ) RETURNS opaque AS
'/usr/doc/postgresql-6.5.2/contrib/spi/refint.so' LANGUAGE 'C';
(Please check for the right folders.)
Then create the script "refint":
#/bin/bash
# Reference integrity workaround for v6.5.3, will be obsolete soon
# Usage: refint primary_table number primary_key foreign_table \
# foreign_key referencing_mode > output_file
# Remark: "number" also can be "" if there is only one foreign table
# for a primary one. "referencing_mode" can be "cascade", "restrict" or
# "setnull".
echo -n CREATE TRIGGER \"$1_ri1$2\" BEFORE DELETE ON \"$1\" FOR EACH ROW
echo -n " EXECUTE PROCEDURE check_foreign_key ('1', '$6', '$3',"
echo " '$4', '$5');"
echo -n CREATE FUNCTION \"$1_ri2$2\" "(" ")" RETURNS opaque AS
echo -n " 'begin if new.$3 != old.$3 then update $4 set $5 = new.$3"
echo " where $5 = old.$3; end if; return new; end;' LANGUAGE 'plpgsql';"
echo -n CREATE TRIGGER \"$1_ri2$2\" AFTER UPDATE ON \"$1\" FOR EACH ROW
echo " EXECUTE PROCEDURE $1_ri2$2();"
echo -n CREATE TRIGGER \"$1_ri3$2\" BEFORE INSERT OR UPDATE ON \"$4\"
echo -n " FOR EACH ROW EXECUTE PROCEDURE check_primary_key"
echo " ('$5', '$1', '$3');"
---------------------------- end of script ----------------------------
After creating this, you can create the reference integrity check with
refint staff 1 packageid package id restrict > refint.sql
By the end, start "psql" with your database and load the refint.sql file:
\i refint.sql
I hope this will help you.
Regards,
Zoltan
From | Date | Subject | |
---|---|---|---|
Next Message | Vladimir Terziev | 2000-02-16 14:06:12 | Re: BSC |
Previous Message | sszabo | 2000-02-16 12:55:22 | Re: pgsql-sql-digest V1 #499 |