Re: enhance SPI to support EXECUTE commands - Mailing list pgsql-hackers

On 2019/9/5 17:33, Pavel Stehule wrote:
>
>
> čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang
> <zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>> napsal:
>
>     On 2019/9/5 16:31, Pavel Stehule wrote:
>      >
>      >
>      > čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
>      > <zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>
>      > <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>>> napsal:
>      >
>      >     On 2019/9/5 15:09, Pavel Stehule wrote:
>      >      >
>      >      >
>      >      > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
>      >      > <zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>
>      >     <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>>
>      >      > <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>
>      >     <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.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
>      >      >
>      >
>