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

Matthias Apitz <guru@unixarea.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