Message-ID: <ED4E30DD9C43D5118DFB00508BBBA76EB166C2@neptun.sonorys.at>
From: =?ISO-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at>
To: pgsql-sql@postgresql.org
Subject: Function to either return one or all records
Date: Wed, 20 Apr 2005 14:41:50 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-1"

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