Thread: Bug: ECPG: Cannot use CREATE AS EXECUTE statemnt

Meskes-san

This thread is branched from the following.
https://www.postgresql.org/message-id/03040DFF97E6E54E88D3BFEE5F5480F74ABEADE7@G01JPEXMBYT04

> > Type1. Bugs or intentional unsupported features.
> >   - EXPLAIN EXECUTE
> >   - **CREATE TABLE AS with using clause**

I noticed that CREATE AS EXECUTE with using clause needs a new
implementation that all parameters in using clause must be embedded into
expr-list of EXECUTE in text-format as the following because there is
no interface of protocol for our purpose. 
It spends more time for implementing. Do you have any advice?

  int id = 100;
  EXEC SQL CREATE TABLE test AS EXECUTE stmt using :id;
  -->
  PQexec("CREATE TABLE test AS EXECUTE stmt(100)");


e.g. PQexecParamas("CREATE TABLE test AS EXECUTE stmt", {23,0},{"100",0},{3,0},NULL)
     It sends the following.

    To backend> Msg P
    To backend> ""
    To backend> "create table test as execute stmt"
    :
    To backend> Msg B
    To backend> ""
    To backend> ""      ---> It means execute request "create table test as execute stmt" with the value.
    To backend (2#)> 1       But the create statement has no $x. Since the value may be discard.
    To backend (2#)> 0       In result, the following error is occurred.
    To backend (2#)> 1
    To backend (4#)> 3
    To backend> 100
    To backend (2#)> 1
    To backend (2#)> 0
    :
    2019-06-06 07:26:35.252 UTC [1630] ERROR:  wrong number of parameters for prepared statement "stmt"
    2019-06-06 07:26:35.252 UTC [1630] DETAIL:  Expected 1 parameters but got 0.
    2019-06-06 07:26:35.252 UTC [1630] STATEMENT:  create table test2 as execute stmt

Regards
Ryo Matsumura

Matsumura-san,

> I noticed that CREATE AS EXECUTE with using clause needs a new
> implementation that all parameters in using clause must be embedded
> into
> expr-list of EXECUTE in text-format as the following because there is
> no interface of protocol for our purpose. 
> It spends more time for implementing. Do you have any advice?
> ...

Unfortunately no, I have no advice. Originally all statements needed
this treatment. :)

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL

Meskes-san

Thank you for your comment.

I attach a patch.
It doesn't include tests, but it passed some test(*1).

Explanation about the patch:

- Add a new ECPGst_exec_embedded_in_other_stmt whether EXECUTE
  statement has exprlist or not.

  This type name may not be good.
  It is a type for [CREATE TABLE ... AS EXECUTE ...].
  But I doesn't consider about [EXPLAIN EXECUTE ...].

- If statement type is a new one, ecpglib embeds variables into 
  query in text format at ecpg_build_params().
  Even if the statement does not have exprlist, ecpglib makes
  exprlist and embeds into it.
  The list is expanded incrementally in loop of ecpg_build_params().

- ecpg_build_params() is difficult to read and insert the above
  logic. Therefore, I refactor it. The deitail is described in comments.

(*1) The followings run expectively.
  exec sql create table if not exists foo (c1 int);
  exec sql insert into foo select generate_series(1, 20);
  exec sql prepare st as select * from foo where c1 % $1 = 0 and c1 % $2 = 0;

  exec sql execute st using :v1,:v2;
  exec sql execute st(:v1,:v2);
  exec sql create table if not exists bar (c1) as execute st(2, 3);
  exec sql create table if not exists bar (c1) as execute st using 2,3;
  exec sql create table if not exists bar (c1) as execute st using :v1,:v2;
  exec sql create table bar (c1) as execute st using :v1,:v2;

Regards
Ryo Matsumura