Re: enhance SPI to support EXECUTE commands

pá 6. 9. 2019 v 3:36 odesílatel Quan Zongliang <
zongliang(dot)quan(at)postgresdata(dot)com> napsal:

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

ok, I can understand to this - but your example is usage is not good.

Pavel

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