Re: [SQL] How to set up a simple relational database - Mailing list 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