From: KÖPFERL 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
Message-ID: <ED4E30DD9C43D5118DFB00508BBBA76EB166C2@neptun.sonorys.at> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robert.koepferl@sonorys.at
Subject: Re: Function to either return one or all records
In-Reply-To: <ED4E30DD9C43D5118DFB00508BBBA76EB166C2@neptun.sonorys.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox