From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partial Indices vs. mixing columns and functions |
Date: | 2001-07-11 08:09:51 |
Message-ID: | 3B4C09CF.11BA268D@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have created table/view pairs like:
CREATE TABLE foo (
key integer not null,
value text not null,
active timestamp not null default now(),
deactive timestamp
);
CREATE VIEW v_foo AS
SELECT * FROM foo WHERE deactive IS NULL;
This allows the user-interface component of the application to query the
v_foo table for selecting "active" records while maintaining a history
of all records for reporting purposes. To enforce uniqueness because
deactive is NULL, I cannot just create an index like:
CREATE UNIQUE INDEX i_foo (value, deactive);
What I can do is create a function like:
CREATE FUNCTION f_foo(oid, timestamp) RETURNS int4 AS '
SELECT 0 WHERE $2 IS NULL
UNION
SELECT oid WHERE $2 IS NOT NULL;
' LANGUAGE 'SQL';
And then create a functional index on foo:
CREATE UNIQUE INDEX i_foo( f_foo(oid, deactive) );
To enforce uniqueness on "active" 'value' columns, I could rewrite the
function to something like:
CREATE FUNCTION f_foo(oid, text, timestamp) RETURNS text AS '
SELECT '0_'||$2 WHERE $3 IS NULL
UNION
SELECT oid::text||'_'||$2 WHERE $3 IS NOT NULL;
' LANGUAGE 'SQL';
but that seems like a real hack and would require a new function for
each table where the unique constraint varies in columns and types. I
could, of course, have 2 tables and a view - 1 for active objects, 1 for
deactive objects, and a view unionizing the 2 together for joins for
reporting purposes. But I humbly request a new feature instead: :-)
Allow for the intermixing of columns and functions in the index
specification so I could write something like:
CREATE UNIQUE INDEX i_foo(value, f_foo(oid, deactive));
Or will Martijn van Oosterhout's new Partial Indices work allow me to
create a unique index like:
CREATE UNIQUE INDEX i_foo ON foo(value)
WHERE deactive IS NULL;
??
That would solve all my problems and answer all my questions...
Mike Mascari
mascarm(at)mascari(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2001-07-11 08:32:07 | Re: Partial Indices vs. mixing columns and functions |
Previous Message | J.H.M. Dassen Ray | 2001-07-11 07:55:53 | Re: PostgreSQL hosting in the Netherlands |