On 01/01/2016 07:47 PM, Dane Foster wrote:
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
And the benefit is?
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?
Yes, use a table:) I guess it comes down to the first question above and
what you are trying to achieve by moving to hstore. I use hstore and it
is very handy for storing ad-hoc data, however when I want all the the
RI whistle and bells I use table structures. The work has been done for
me by folks who know a lot more about this then I and it is one less
thing for me to code/worry about.
Thank you for your consideration,
Dane
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
