Hi all,
I think I have got a usual problem. I'm asking here, however, because I
wonder why it works this way.
The problem is to write a fcn that eihter returns all records or just
one/none filtered by some expression. For example get a value by id or
return all values if the given id is null.
For a table like this (id (PK) int | val int ) one would write a function
like this:
CREATE OR REPLACE FUNCTION getval(integer) RETURNS SETOF id_val_tbl AS
$BODY$
select * from id_bal_tbl where ( $1 is null )or (id=$1 );
$BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
It works fine, however an index is never used (if just one record is
requested). The column id has a btree-Index but what aobut it. I'm wondering
how this comes and how one can overcome this limit.
I read that btree and null values do not like each other very well. And
surely, if the Select is transformed reduced to
select * from id_bal_tbl where (false)or (id=$1 );
the Index is used!
What I don't get is: When expanding parameters in the SQL-function it will
evaluate to the statement above. Bevore any data is looked up. Why is that?
Thanks again in advance