Re: Help with optional parameters - Mailing list pgsql-sql

From Curtis Scheer
Subject Re: Help with optional parameters
Date
Msg-id 031936836C46D611BB1B00508BE7345D04AD244C@gatekeeper.daycos.com
Whole thread Raw
In response to Help with optional parameters  ("Rob Tester" <robtester@gmail.com>)
Responses Re: Help with optional parameters
List pgsql-sql

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;

 

pgsql-sql by date:

Previous
From: Markus Schaber
Date:
Subject: Re: unique rows
Next
From: MaXX
Date:
Subject: Re: Help with optional parameters