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