enhance SPI to support EXECUTE commands

Lists: pgsql-hackers
From: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: enhance SPI to support EXECUTE commands
Date: 2019-09-05 06:39:00
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

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.

Regards,
Quan Zongliang

Attachment Content-Type Size
spi_execmd.patch text/plain 947 bytes

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-05 07:09:26
Message-ID: CAFj8pRAVT-eLKfCMr564RYcAsJoQtC22vqn-m5uAFCSFaLyXTg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang <
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?

Regards

Pavel

> Regards,
> Quan Zongliang
>


From: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-05 08:25:15
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

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

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
>


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-05 08:31:19
Message-ID: CAFj8pRDtLLQDrMBD5cnM3YF6QeWKbXzjihVNFg9JccsU+-K4fA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang <
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>> 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?

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


From: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-05 08:56:48
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-05 09:33:34
Message-ID: CAFj8pRB5Tr28bEKTP9TXTaqOEJ6gjeZsmtnxNoM0vte-PBfH7w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang <
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>> 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>>> 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.

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


From: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-06 01:35:51
Message-ID: [email protected]
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-06 03:18:58
Message-ID: CAFj8pRBjSLa9=WnjXWvKR8nkZusg690JFzE3n2i4=OjFDUrJhw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Ahsan Hadi <ahsan(dot)hadi(at)gmail(dot)com>
To: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2019-09-18 12:29:52
Message-ID: CA+9bhCKc4dd=VEpEPCMJfwB2Ho0W5zPzQj+AVdxE8FNxawxPEQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

I don't see much use for this because the documentation says that "server's
execute command cannot be used directly within pl/pgsql function (and it is
not needed). Within pl/pgsql you can execute update/delete commands using
pl/pgsql EXECUTE command and get results like row_count using "get
diagnostic".

Why would somebody do what you have shown in your example in pl/pgsql? Or
do you have a more general use-case for this enhancement?

On Thu, Sep 5, 2019 at 11:39 AM Quan Zongliang <
zongliang(dot)quan(at)postgresdata(dot)com> wrote:

> 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.
>
> Regards,
> Quan Zongliang
>

--
Highgo Software (Canada/China/Pakistan)
URL : http://www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
EMAIL: mailto: ahsan(dot)hadi(at)highgo(dot)ca


From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Ahsan Hadi <ahsan(dot)hadi(at)gmail(dot)com>
Cc: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2020-01-04 23:05:28
Message-ID: 20200104230528.is55vhgto34ac4dw@development
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

Hi,

On Wed, Sep 18, 2019 at 05:29:52PM +0500, Ahsan Hadi wrote:
>I don't see much use for this because the documentation says that "server's
>execute command cannot be used directly within pl/pgsql function (and it is
>not needed). Within pl/pgsql you can execute update/delete commands using
>pl/pgsql EXECUTE command and get results like row_count using "get
>diagnostic".
>
>Why would somebody do what you have shown in your example in pl/pgsql? Or
>do you have a more general use-case for this enhancement?
>

Yeah, I think that's a good question - why would we need this? In fact,
the plpgsql docs explicitly say:

The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL
statement supported by the PostgreSQL server. The server's EXECUTE
statement cannot be used directly within PL/pgSQL functions (and is
not needed).

That is because all queries in plpgsql are prepared and cached
automatically, so why would we need this feature?

In any case, the patch should probably be in "waiting on author" state,
so I'll make it that way.

registrace

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Ahsan Hadi <ahsan(dot)hadi(at)gmail(dot)com>
Cc: Quan Zongliang <zongliang(dot)quan(at)postgresdata(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enhance SPI to support EXECUTE commands
Date: 2020-02-01 11:21:05
Message-ID: 20200201112105.rvudjcllv5wfaklq@development
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

I've marked this patch as returned with feedback. It's been sitting in
the CF without any response from the author since September, and it's
not quite clear we actually want/need this feature. If needed, the patch
can be resubmitted for 2020-03.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services