Re: DBI and placeholders question

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(at)postgresql(dot)org)