Re: postgresql wildcard when parameter is -1

On Thu, Aug 24, 2006 at 09:01:06AM +0100, ben sewell wrote:
> I have a question, when a parameter is recieved as -1 (as an integer from
> Access), I would like that parameter to be a wildcard. The logic is that I
> am sending the primary key for a table to postgres for a report but when
> there hasnt been a value chosen in the combo box -1 is sent to postgres so
> it would mean that that parameter don't apply in the SP, otherwise if it did
> have another value then the parameter would be used to do some filtering
> (joins).

You'll need to rewrite the query to handle the "wildcard". One way
would be with an expression like this:

WHERE (param = -1 OR param = column_name) AND ...

The parentheses are important if you have multiple expressions.

Another way would be to build the query string dynamically, adding
only the parts you need, then EXECUTE it (assuming PL/pgSQL; do the
equivalent in other languages). See "Executing Dynamic Commands"
in the PL/pgSQL documentation:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

--
Michael Fuhr