Re: enhance SPI to support EXECUTE commands

On 2019/9/5 17:33, Pavel Stehule wrote:
>
>
> čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang
> <zongliang(dot)quan(at)postgresdata(dot)com
> <mailto:zongliang(dot)quan(at)postgresdata(dot)com>> napsal:
>
> On 2019/9/5 16:31, Pavel Stehule wrote:
> >
> >
> > čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
> > <zongliang(dot)quan(at)postgresdata(dot)com
> <mailto:zongliang(dot)quan(at)postgresdata(dot)com>
> > <mailto:zongliang(dot)quan(at)postgresdata(dot)com
> <mailto:zongliang(dot)quan(at)postgresdata(dot)com>>> napsal:
> >
> >     On 2019/9/5 15:09, Pavel Stehule wrote:
> >      >
> >      >
> >      > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
> >      > <zongliang(dot)quan(at)postgresdata(dot)com
> <mailto:zongliang(dot)quan(at)postgresdata(dot)com>
> >     <mailto:zongliang(dot)quan(at)postgresdata(dot)com
> <mailto:zongliang(dot)quan(at)postgresdata(dot)com>>
> >      > <mailto:zongliang(dot)quan(at)postgresdata(dot)com
> <mailto:zongliang(dot)quan(at)postgresdata(dot)com>
> >     <mailto:zongliang(dot)quan(at)postgresdata(dot)com
> <mailto:zongliang(dot)quan(at)postgresdata(dot)com>>>> napsal:
> >      >
> >      >     Dear hackers,
> >      >
> >      >     I found that such a statement would get 0 in PL/pgSQL.
> >      >
> >      >     PREPARE smt_del(int) AS DELETE FROM t1;
> >      >     EXECUTE 'EXECUTE smt_del(100)';
> >      >     GET DIAGNOSTICS j = ROW_COUNT;
> >      >
> >      >     In fact, this is a problem with SPI, it does not support
> >     getting result
> >      >     of the EXECUTE command. I made a little enhancement.
> Support
> >     for the
> >      >     number of rows processed when executing
> INSERT/UPDATE/DELETE
> >     statements
> >      >     dynamically.
> >      >
> >      >
> >      > Is there some use case for support this feature?
> >      >
> >     A user deletes the data in PL/pgSQL using the above method,
> hoping
> >     to do
> >     more processing according to the number of rows affected, and
> found
> >     that
> >     each time will get 0.
> >
> >     Sample code:
> >     PREPARE smt_del(int) AS DELETE FROM t1 WHERE c=$1;
> >     EXECUTE 'EXECUTE smt_del(100)';
> >     GET DIAGNOSTICS j = ROW_COUNT;
> >
> >
> > This has not sense in plpgsql. Why you use PREPARE statement
> explicitly?
> >
> Yes, I told him to do it in other ways, and the problem has been solved.
>
> Under psql, we can get this result
>
> flying=# EXECUTE smt_del(100);
> DELETE 1
>
> So I think this may be the negligence of SPI, it should be better to
> deal with it.
>
>
> Personally, I would not to support features that allows bad code.
>
My code is actually a way to continue the CREATE AS SELECT and COPY
statements. In spi.c, they look like this:

if (IsA(stmt->utilityStmt, CreateTableAsStmt)) // original code
...
else if (IsA(stmt->utilityStmt, CopyStmt)) // original code
...
else if (IsA(stmt->utilityStmt, ExecuteStmt)) // my code

My patch was not developed for this PL/pgSQL approach. I just because it
found this problem.

> Pavel
>
>
> >
> >     IF j=1 THEN
> >         do something
> >     ELSIF j=0 THEN
> >         do something
> >
> >     Here j is always equal to 0.
> >
> >
> >
> >     Regards
> >
> >      > Regards
> >      >
> >      > Pavel
> >      >
> >      >
> >      >     Regards,
> >      >     Quan Zongliang
> >      >
> >
>