Re: function currtid2() in SQL and ESQL/C to get the new CTID of a row

Matthias Apitz <guru(at)unixarea(dot)de> writes:
> So far so good, but we do need this in ESQL/C. There the code looks as:

> EXEC SQL BEGIN DECLARE SECTION;
> char stmt[255];
> static char newCTID[80];
> EXEC SQL END DECLARE SECTION;

> memset(stmt, 0, sizeof(stmt));
> sprintf(stmt, "currtid2('%s'::text, '%s'::tid)", table, oldCTID);
> fprintf(stderr, stmt);
> fprintf(stderr, "\n");

> EXEC SQL SELECT :stmt INTO :newCTID;

> sprintf(stmt, "table %s oldCTID %s newCTID %s\n",
> table, oldCTID, newCTID);
> fprintf(stderr, stmt);

> The code runs fine but the content of the host variable is the statement
> itself 'currtid2('dbctest'::text, '(0,13)'::tid)' like the SELECT was
> just an echo function.

Indeed.

> Is this function currtid2() not meant to be used in ESQL/C? Or did we
> something wrong in ESQL/C?

This is not about currtid2, this is a fundamental misunderstanding
of how ECPG works. You can only inject data values into ordinary
EXEC SQL commands. I think you could handle this as

EXEC SQL SELECT currtid2(:table ::text, :oldCTID ::tid) INTO :newCTID;

If you want full-on dynamic SQL, that's also possible but you'd need
PREPARE/EXECUTE, and it wouldn't look much like this fragment.
I don't see a need for that here, though.

regards, tom lane