On Sat, Jan 2, 2016 at 10:59 AM, Bill Moran <wmo...@potentialtech.com> wrote:
> On Sat, 2 Jan 2016 07:30:38 -0800 > Adrian Klaver <adrian.kla...@aklaver.com> 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
