From: Curtis Scheer <Curtis@DAYCOS.com>
To: pgsql-sql@postgresql.org
Cc: Rob Tester <robtester@gmail.com>
Subject: Re: Help with optional parameters
Date: Thu, 21 Sep 2006 17:18:48 -0500
Message-ID: <031936836C46D611BB1B00508BE7345D04AD244C@gatekeeper.daycos.com> (raw)
I noticed this one by searching in the archives, as I am working with some
"optional" parameters myself and noticed your solution. I just wanted to
make one improvement suggestion which is instead of checking whether or not
a parameter has been used simply start your query like so
Query_base := 'SELECT * FROM my_table WHERE 1 =1;
If you do that then you can just add on any parameters you need or not add
any at all. I think that seems to be a bit simpler than having a "has_param"
Boolean variable.
Thanks,
Curtis
_____
From: Rob Tester [mailto:robtester@gmail.com]
Sent: Thursday, August 17, 2006 8:58 PM
To: MaXX
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with optional parameters
BEGIN
query_base := 'SELECT * FROM my_table ';
has_param := FALSE;
query_where := '';
IF (a IS NOT NULL) THEN
IF (has_param IS FALSE)THEN
-- there is no param yet add WHERE to the query
query_where := ' WHERE ';
ELSE
-- there is already something in the WHERE clause, we need to add AND
query_where := query_where || ' AND ';
END IF;
query_where := query_where || 'parama='||a;
--beware if param quoting is required
has_param := TRUE; -- now there is at least 1 param
END IF;
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: Curtis@DAYCOS.com, robtester@gmail.com
Subject: Re: Help with optional parameters
In-Reply-To: <031936836C46D611BB1B00508BE7345D04AD244C@gatekeeper.daycos.com>
* 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