Обсуждение: prepared query plan did not update

Good morning,

I tried to use prepared query plan to update columns, but it did not 
update at all.

PREPARE pname(varchar) AS
UPDATE t1
SET    col1 = false
WHERE  col1 AND       col2 = '$1' ;
EXECUTE pname( 'value' )



Could someone tell me where I did wrong please?

Thanks alot!

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu
Sent: Wednesday, September 17, 2008 2:55 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] prepared query plan did not update

Good morning,

I tried to use prepared query plan to update columns, but it did not
update at all.

PREPARE pname(varchar) AS
UPDATE t1
SET    col1 = false
WHERE  col1 AND       col2 = '$1' ;
EXECUTE pname( 'value' )



Could someone tell me where I did wrong please?

Thanks alot!



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


WHERE  col1 AND       col2 = '$1' ;
Are you looking for both: col1 and col2 - to be equal to '$1'?
Then it should be:
WHERE  col1 = '$1' AND       col2 = '$1';

> I tried to use prepared query plan to update columns, but it did not
> update at all.
> 
> PREPARE pname(varchar) AS
> UPDATE t1
> SET    col1 = false
> WHERE  col1 AND
>         col2 = '$1' ;
> EXECUTE pname( 'value' )
> Could someone tell me where I did wrong please?
> 
> 
> 
> 
> 
> WHERE  col1 AND
>         col2 = '$1' ;
> Are you looking for both: col1 and col2 - to be equal to '$1'?
> Then it should be:
> WHERE  col1 = '$1' AND
>         col2 = '$1';

Not exactly, col1 is boolean value

WHERE col1 = true AND      col2 = '$1';

On Wed, 17 Sep 2008, Emi Lu wrote:

> Good morning,
>
> I tried to use prepared query plan to update columns, but it did not
> update at all.
>
> PREPARE pname(varchar) AS
> UPDATE t1
> SET    col1 = false
> WHERE  col1 AND
>         col2 = '$1' ;

I don't think you want those quotes in the second part of the where
clause. I'm pretty sure that means you're comparing against the literal
string with a dollar sign and one rather than the value given at execute
time for $1.

Stephan Szabo wrote:
> On Wed, 17 Sep 2008, Emi Lu wrote:
> 
>> Good morning,
>>
>> I tried to use prepared query plan to update columns, but it did not
>> update at all.
>>
>> PREPARE pname(varchar) AS
>> UPDATE t1
>> SET    col1 = false
>> WHERE  col1 AND
>>         col2 = '$1' ;
> 
> I don't think you want those quotes in the second part of the where
> clause. I'm pretty sure that means you're comparing against the literal
> string with a dollar sign and one rather than the value given at execute
> time for $1.

Do you mean:

PREPARE pname(varchar) AS
UPDATE t1
SET   col1 = false
WHERE col1 AND      col2 = $1 ;

But still does not work? Strange, right?

On Wed, 17 Sep 2008, Emi Lu wrote:

> Stephan Szabo wrote:
> > On Wed, 17 Sep 2008, Emi Lu wrote:
> >
> >> Good morning,
> >>
> >> I tried to use prepared query plan to update columns, but it did not
> >> update at all.
> >>
> >> PREPARE pname(varchar) AS
> >> UPDATE t1
> >> SET    col1 = false
> >> WHERE  col1 AND
> >>         col2 = '$1' ;
> >
> > I don't think you want those quotes in the second part of the where
> > clause. I'm pretty sure that means you're comparing against the literal
> > string with a dollar sign and one rather than the value given at execute
> > time for $1.
>
> Do you mean:
>
> PREPARE pname(varchar) AS
> UPDATE t1
> SET   col1 = false
> WHERE col1 AND
>        col2 = $1 ;
>
> But still does not work? Strange, right?

I think we'll need to see a complete example with table definitions and
sample data because it does appear to work for me in simple tests.