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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
