From: | Dane Foster <studdugie(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Enforcing referential integrity against a HSTORE column |
Date: | 2016-01-02 03:47:39 |
Message-ID: | CA+WxinJi+3t+tGmdZjB8Y9JmipMp_9nmuv0uS=1a5KHbJ7_8OA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I'm moving a MySQL database to PostgreSQL and redesigning parts of it to
take advantage of PostgreSQL's richer type system and other advance
features. Currently I am attempting to replace a table of name/value pair
data w/ a hstore column. But now that the data will no longer be flattened
out in a table I need to manually handle referential integrity
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?
Thank you for your consideration,
Dane
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-01-02 15:30:38 | Re: Enforcing referential integrity against a HSTORE column |
Previous Message | Andrew Bailey | 2016-01-02 03:39:16 | Efficiently selecting single row from a select with window functions row_number, lag and lead |