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

From Quan Zongliang
Subject Re: enhance SPI to support EXECUTE commands
Date
Msg-id a082aea1-406c-2abd-c380-c31c685e236a@postgresdata.com
Whole thread Raw
In response to enhance SPI to support EXECUTE commands  (Quan Zongliang <zongliang.quan@postgresdata.com>)
Responses Re: enhance SPI to support EXECUTE commands
List pgsql-hackers
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>> 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>>> 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.

> 
>     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
>      >
> 




pgsql-hackers by date:

Previous
From: "Jamison, Kirk"
Date:
Subject: RE: [PATCH] Speedup truncates of relation forks
Next
From: Dilip Kumar
Date:
Subject: Re: CVE-2017-7484-induced bugs, or, btree cmp functions are not leakproof?