Re: DBI and placeholders question - Mailing list pgsql-general

From Ragnar Hafstað
Subject Re: DBI and placeholders question
Date
Msg-id 1108411095.6617.59.camel@localhost.localdomain
Whole thread Raw
In response to DBI and placeholders question  (mike <mike@redtux1.uklinux.net>)
List pgsql-general
On Mon, 2005-02-14 at 18:41 +0000, mike wrote:
> Is it possible to have a placeholder on the left side of a select
> criteria?

yes

> eg:
>
> SELECT CASE WHEN date_part('dow',?::date)=5
>
> this bit is fine
>
> THEN CASE WHEN ? = 2 OR ? =3 OR ?=6 OR ?=7 OR ?=8 OR ?=12 THEN '7:00'
>
> here the ? is being read as NULL ie, output from LOG
>
> SELECT CASE WHEN date_part('dow', '2005-02-28'::date)=5 THEN CASE WHEN
> NULL = 2 OR  NULL =3 OR  NULL=6 OR  NULL=7 OR  NULL=8 OR  NULL=12 THEN
> '7:00' WHEN  NULL = 1 OR  NULL =5 OR  NULL=13 THEN '3:30' ELSE  NULL
> END

please show us a minimal case that behaves as you say.

are you saying this only happens in nested 'CASE' ?

does this only happen if you use such a long a long series
of ?=val1 OR ?=val2 .....

what actual values did you use for the question marks?

this seems to work for me:
  #!/usr/bin/perl
  my DBI;
  my $dbh = DBI->connect('dbi:Pg:dbname=test')
    or die 'no database connection';
  my $sql=q{select CASE WHEN ? = 1 THEN CASE
              WHEN ? = '2' OR ? = 3 THEN   'YEP' ELSE 'NOPE' END END};
  my ($v)=$dbh->selectrow_array($sql, {}, 1,2,3);
  print "$v\n";

gnari








> I am getting the ? translated as nulls
>
> apologies for off-topic, but any help appreciated
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: possible bug with compound index.
Next
From: "Cristian Prieto"
Date:
Subject: More info about PostgreSQL 8