On Sat, Jan 2, 2016 at 10:59 AM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
On Sat, 2 Jan 2016 07:30:38 -0800
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
> > So given:
> >
> > CREATE TABLE xtra_fields(
> > xfk SERIAL PRIMARY KEY,
> > xtk INTEGER NOT NULL REFERENCES xtra_types,
> > ...
> > );
> >
> > CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$
> > WITH keyz AS (SELECT skeys($1)::INT AS xfk)
> > SELECT
> > (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk))
> > =
> > (SELECT COUNT(*) FROM keyz)
> > $$LANGUAGE SQL STABLE STRICT LEAKPROOF;
> >
> > CREATE TABLE foo(
> > id INTEGER NOT NULL CHECK (id > 0),
> > ...
> > -- Extra fields where the keys are the xtra_fields.xfk values and the
> > values are the
> > -- data values for the specific xfk.
> > xtra hstore CHECK (foo_xtra_fk(xtra))
> > );
> >
> > is ?there a more efficient way of maintaining logical referential integrity?
I second Adrian's comment on making sure that the benefit of HSTORE is
outweighing the drawback of having to write your own checks ... however,
if you decide that HSTORE is the right way to go, you may want to try
something more along the lines of this for your check:
SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN (akeys($1)));
I also agree w/ Adrian's comments and to that end I will be keeping the original tables and building a view that does what I need on top of them. But I really like your SQL solution. It is quite elegant and I suspect it would run faster than mine for a large enough data set. So I'll be filing it away in memory for use at some future point in time.
Not tested, so it's possible that I have some typo or something; but overall
I've found that the NOT EXISTS construct can be very efficient in cases
like these.
--
Bill Moran
Thanks,
Dane
