: Mittwoch, 20. April 2005 23:06
|To: Tambet Matiisen
|Cc: KÖPFERL Robert; pgsql-sql@postgresql.org
|Subject: Re: [SQL] Function to either return one or all records
|
|
|Tambet Matiisen [EMAIL PROTECTED] writes:
| Standard technique is to rewrite OR queries to UNION
|queries. I believe PostgreSQL
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 21, 2005 12:06 AM
To: Tambet Matiisen
Cc: KPFERL Robert; pgsql-sql@postgresql.org
Subject: Re: [SQL] Function to either return one or all records
Tambet Matiisen [EMAIL PROTECTED] writes
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.
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
Tambet Matiisen [EMAIL PROTECTED] writes:
Standard technique is to rewrite OR queries to UNION queries. I believe
PostgreSQL optimizer does not do that automatically. So you could try instead:
select * from id_bal_tbl where $1 is null
union all
select * from id_bal_tbl where id = $1;
...
