Thread: postgresql wildcard when parameter is -1

Hi guys,
I'm still running into problems on my front end, but thats because of access, not postgres ;)

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).

Any ideas? Feel free to ask any questions if you want.

Cheers,
Ben

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

Hi Michael,

On Fri, Aug 25, 2006 at 07:42:46AM +0100, ben sewell wrote:
> thanks for your reply. I've seen that % is the wildcard in postgres, so
> couldnt I just do an if statement to overwrite the parameter? Also, I'm
> interested in a wildcard for dates. Would that be %%/%%/%%%%?

The % character is a wildcard in text pattern matching; if you want
to use it to match non-text data then you'll have to cast that data
to text.  Many common types have implicit casts to text so you might
be able to rely on that, although an explicit cast would make your
intentions more clear.  However, now all your expressions would
have to use LIKE, which won't use non-text indexes.  You could
address that by creating indexes on cast-to-text expressions, but
that has implications on storage space and performance of inserts,
updates, and deletes.  You'll also have to consider whether pattern
matching instead of exact matching might yield unexpected results.

In pattern matching the % character matches zero or more characters,
so if your date output format is dd/mm/yyyy or mm/dd/yyyy then %/%/%
should work, or for yyyy-mm-dd then %-%-%.  The underscore (_)
matches exactly one character, so you could also use __/__/____ or
____-__-__.

--
Michael Fuhr