After doing some research I was able to solve the problem. My findings:
Like you reproduced, this doesn't work, giving the initial error:
--DEALLOCATE foo;
prepare foo as
with newValues (id_tes, tes_textid, "number") as
( values
($1, $2, $4),
($1, $3, $5)
),
upsert as
(
update tbl_test lm
set tes_textid = nv.tes_textid
from newValues nv
where lm.id_tes = nv.id_tes returning lm.id_tes
) INSERT INTO tbl_test (tes_textid, "number")
select tes_textid, "number"
from newValues where not exists (select id_tes from upsert
up where up.id_tes = newValues.id_tes );
EXECUTE foo(0, 'en','es' ,4 ,5);
This works (adding the parameter types on the prepare declaration):
prepare foo (integer,text,text,integer,integer) as
with newValues (id_tes, tes_textid, "number") as
( values
($1, $2, $4),
($1, $3, $5)
),
upsert as
(
update tbl_test lm
set tes_textid = nv.tes_textid
from newValues nv
where lm.id_tes = nv.id_tes returning lm.id_tes
) INSERT INTO tbl_test (tes_textid, "number")
select tes_textid, "number"
from newValues where not exists (select id_tes from upsert
up where up.id_tes = newValues.id_tes );
EXECUTE foo(0, 'en','es' ,4 ,5);
This works too (casting the parameters):
prepare foo as
with newValues (id_tes, tes_textid, "number") as
( values
($1::integer, $2, $4::integer),
($1::integer, $3, $5::integer)
),
upsert as
(
update tbl_test lm
set tes_textid = nv.tes_textid
from newValues nv
where lm.id_tes = nv.id_tes returning lm.id_tes
) INSERT INTO tbl_test (tes_textid, "number")
select tes_textid, "number"
from newValues where not exists (select id_tes from upsert
up where up.id_tes = newValues.id_tes );
EXECUTE foo(0, 'en','es' ,4 ,5);
I will go with this last option.
Thanks for the help and the comments.
Regards,
Joan
On 18/12/2014 13:09, Tommi Mäkitalo wrote:
> Hi,
>
> first of all the short answer: no.
>
> Tntdb do not replace the values in the sql at all. Postgresql does that.
> There is a prepared statement api where placeholders are used. In
> postgresql the placeholders are $1, $2, and so on. And that is what you
> can see in the logs. There are separate statements, which send the
> values to postgresql. Therefore it makes sense to reuse prepared
> statements since they are then parsed just once by postgresql and can be
> executed multiple times with different parameters.
>
> I looked at your query and I must confess, that I am not familar with
> upsert in postgresql. But I took the statement and replaced the
> placeholders with postgresql syntax and executing that with psql gives
> me the same error. Here is my sql:
>
> prepare foo as
> with newValues (id_tes, tes_textid, number) as
> ( values
> ($1, $2, $3),
> ($4, $5, $6)
> ),
> upsert as
> (
> update tbl_test lm
> set tes_textid = nv.tes_textid
> from newValues nv
> where lm.id_tes = nv.id_tes returning lm.id_tes
> ) INSERT INTO tbl_test (tes_textid, number)
> select tes_textid, number
> from newValues where not exists (select id_tes from upsert
> up where up.id_tes = newValues.id_tes );
>
> So it really does not look like a tntdb problem.
>
> As a side note I have some comments though:
>
> * Do not end the sql statement with a semicolon in tntdb. The semicolon
> is just needed by psql but do not belong to the actual statement.
> Postgresql do not complain that but it just do not belong there.
> * Instead of setInt, setString et al you should use the overloaded set.
> And for syntactic sugar you can chain the calls:
> query.set("a", 0)
> .set("d", 0)
> .set("b", "es")
> .set("e", "en")
> .set("c", 1)
> .set("f", 2);
> * if you use localhost, you should not specify the host in the dburl.
> Postgresql uses named pipes and can even use your unix login for
> authentication. You don't even need to specify the username and
> password. By default postgresql do not even enable tcp/ip. And it is not
> needed when using named pipes.
>
>
> Tommi
>
>
>
> Am 17.12.2014 um 00:11 schrieb Jouven:
>> Greetings,
>>
>> Is there a way to output a prepared statement with the values already
>> replaced? (as in just before being sent to the DB)
>> Setting the logging to cxxtools::Logger::DEBUG prints the query but the
>> parameters change to something like "$1", "$2"...
>>
>> Anyway I think found a bug, or a lack of a feature, I'm trying to launch
>> an "upsert" using a CTE (common table expression) against a postgres
>> 9.4rc1 database.
>> If I write the values on the string the query works but If I use
>> substitution I keep getting errors, like this one:
>> Postgresql-Error 42883: operator does not exist: integer = text at 209
>> in PQprepare
>> Removing substitution step by step and using direct values moves the
>> error to the next field that still uses substitution, until there is
>> none left and it works.
>>
>> code: http://pastebin.com/xVxcqEHU (includes the sql for the table
>> creation and the same query to use directly on a client like psql)
>>
>> I'm using the latest tntdb code pulled from github.
>>
>> Regards,
>> Joan
>>
>> ------------------------------------------------------------------------------
>> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>> with Interactivity, Sharing, Native Excel Exports, App Integration & more
>> Get technology previously reserved for billion-dollar corporations, FREE
>> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Tntnet-general mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/tntnet-general
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
> _______________________________________________
> Tntnet-general mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/tntnet-general
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Tntnet-general mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tntnet-general
