When I execute following query in PG Admin III, it runs fine:
WITH new_values (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5) AS (
VALUES (current_timestamp,'2','3','4','5','6','7','8','9')
),
UPSERT AS (
UPDATE utel u
SET
updated_at=nv.updated_at,
v0=nv.v0,v1=nv.v1,v2=nv.v2,v3=nv.v3,v4=nv.v4,v5=nv.v5
FROM
new_values nv
WHERE
u.talker_id=nv.talker_id AND u.sentence_id=nv.sentence_id RETURNING u.*
)
INSERT into utel (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5)
SELECT updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5
FROM new_values WHERE NOT EXISTS(
SELECT 1 FROM upsert up
WHERE
up.talker_id=new_values.talker_id
AND up.sentence_id=new_values.sentence_id
)
but when I try to execute it through TFDQuery, PostgreSQL complains:
syntax error at or near "INSERT" at character 355
I turned on PostgreSQL logging to Windows error log and saw following:
DECLARE "10STM" CURSOR WITH HOLD FOR
WITH new_values (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5) AS (
VALUES (current_timestamp,'2','3','4','5','6','7','8','9')
),
UPSERT AS (
UPDATE utel u
SET
updated_at=nv.updated_at,
v0=nv.v0,v1=nv.v1,v2=nv.v2,v3=nv.v3,v4=nv.v4,v5=nv.v5
FROM
new_values nv
WHERE
u.talker_id=nv.talker_id AND u.sentence_id=nv.sentence_id RETURNING u.*
)
INSERT into utel (updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5)
SELECT updated_at,talker_id,sentence_id,v0,v1,v2,v3,v4,v5
FROM new_values WHERE NOT EXISTS(
SELECT 1 FROM upsert up
WHERE
up.talker_id=new_values.talker_id
AND up.sentence_id=new_values.sentence_id
)
When I execute the same, with DECLARE "10STM" CURSOR WITH HOLD FOR in PG Admin III, I get exact the same error.
Why FireDAC precedes PG query with a cursor and how to avoid this?
Victoria doesn't want to get her points, so here is the answer:
Cursor kind in fetch options was set to ckAutomatic. I changed it to ckDefault and it works now.