Re: SET within a function?

Edmund Dengler wrote:

> This just returns us to the problem that the use of a function causes a
> sequential scan (as the select has to essentially apply the function to
> each row). I would need to store a dummy value into the field (it is an
> int, so I could store -1, but it breaks my sense of aesthetics to do this
> simply to get around the sequential scan).
>
> Could I use a functional index, maybe?

Yes, but I think you have to write a little wrapper:

CREATE TABLE foo (
key integer not null,
value text);

CREATE FUNCTION toValue(text) RETURNS text AS '

SELECT COALESCE($1, '''');

' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX i_foo1 ON foo(toValue(value));

And always be sure to use the function in the query:

SELECT *
FROM foo
WHERE toValue(value) = '';

For fun:

SET enable_seqscan to off;

EXPLAIN SELECT * FROM foo WHERE toValue(value) = 'Mike';

should produce an Index Scan....

HTH,

Mike Mascari
mascarm(at)mascari(dot)com