Partial Indices vs. mixing columns and functions - Mailing list 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@mascari.com