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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
