doc: update PL/pgSQL sample loop function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

doc: update PL/pgSQL sample loop function

Ian Barwick-3
Hi

Here:

   https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

we have a sample PL/PgSQL function (dating from at least 7.2) demonstrating
query result loops, which refreshes some pseudo materialized views stored in
a user-defined table.

As we've had proper materialized views since 9.3, I thought it might
be nice to update this with a self-contained sample which can be used
as-is; see attached patch.

(As a side note the current sample function contains a couple of "%s"
placeholders which should be just "%"; a quick search of plpgsql.sgml
shows this is the only place they occur).

Will submit to the next commitfest.


Regards

Ian Barwick

--
  Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

plpgsql-mviews-sample-func-update.1.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: doc: update PL/pgSQL sample loop function

Pavel Stehule
Hi

čt 29. 8. 2019 v 5:03 odesílatel Ian Barwick <[hidden email]> napsal:
Hi

Here:

   https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

we have a sample PL/PgSQL function (dating from at least 7.2) demonstrating
query result loops, which refreshes some pseudo materialized views stored in
a user-defined table.

As we've had proper materialized views since 9.3, I thought it might
be nice to update this with a self-contained sample which can be used
as-is; see attached patch.

(As a side note the current sample function contains a couple of "%s"
placeholders which should be just "%"; a quick search of plpgsql.sgml
shows this is the only place they occur).

Will submit to the next commitfest.

+1

Pavel



Regards

Ian Barwick

--
  Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: doc: update PL/pgSQL sample loop function

akapila
On Thu, Aug 29, 2019 at 10:07 AM Pavel Stehule <[hidden email]> wrote:

>
> Hi
>
> čt 29. 8. 2019 v 5:03 odesílatel Ian Barwick <[hidden email]> napsal:
>>
>> Hi
>>
>> Here:
>>
>>    https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
>>
>> we have a sample PL/PgSQL function (dating from at least 7.2) demonstrating
>> query result loops, which refreshes some pseudo materialized views stored in
>> a user-defined table.
>>
>> As we've had proper materialized views since 9.3, I thought it might
>> be nice to update this with a self-contained sample which can be used
>> as-is; see attached patch.
>>
>> (As a side note the current sample function contains a couple of "%s"
>> placeholders which should be just "%"; a quick search of plpgsql.sgml
>> shows this is the only place they occur).
>>
>> Will submit to the next commitfest.
>
> +1
>

The current example shows the usage of looping in plpgsql, so as such
there is no correctness issue, but OTOH there is no harm in updating
the example as proposed by Ian Barwick.  Does anyone else see any
problem with this idea?  If we agree to proceed with this update, it
might be better to backpatch it for the sake of consistency though I
am not sure about that.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

Re: doc: update PL/pgSQL sample loop function

akapila
On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <[hidden email]> wrote:
>
> The current example shows the usage of looping in plpgsql, so as such
> there is no correctness issue, but OTOH there is no harm in updating
> the example as proposed by Ian Barwick.  Does anyone else see any
> problem with this idea?  If we agree to proceed with this update, it
> might be better to backpatch it for the sake of consistency though I
> am not sure about that.
>

While checking the patch in back-branches, I noticed that it doesn't
get applied to 9.4 due to the way the example forms the string.  I
have done the required changes for 9.4 as well and attached is the
result.

Ian, if possible, can you once check the patch for 9.4?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

0001-Doc-Update-PL-pgSQL-sample-function-in-plpgsql.sgml.patch (3K) Download Attachment
0001-94-Doc-Update-PL-pgSQL-sample-function-in-plpgsql.sgml.patch (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: doc: update PL/pgSQL sample loop function

Pavel Stehule


st 11. 9. 2019 v 7:45 odesílatel Amit Kapila <[hidden email]> napsal:
On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <[hidden email]> wrote:
>
> The current example shows the usage of looping in plpgsql, so as such
> there is no correctness issue, but OTOH there is no harm in updating
> the example as proposed by Ian Barwick.  Does anyone else see any
> problem with this idea?  If we agree to proceed with this update, it
> might be better to backpatch it for the sake of consistency though I
> am not sure about that.
>

While checking the patch in back-branches, I noticed that it doesn't
get applied to 9.4 due to the way the example forms the string.  I
have done the required changes for 9.4 as well and attached is the
result.


Is question if for this queries should not be used some from information_schema instead direct access to pg_catalog.

But I looked now, and we don't see materialized views in information_schema - what is probably bug.

Pavel
 
Ian, if possible, can you once check the patch for 9.4?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Reply | Threaded
Open this post in threaded view
|

Re: doc: update PL/pgSQL sample loop function

akapila
On Wed, Sep 11, 2019 at 11:40 AM Pavel Stehule <[hidden email]> wrote:

>
> st 11. 9. 2019 v 7:45 odesílatel Amit Kapila <[hidden email]> napsal:
>>
>> On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <[hidden email]> wrote:
>> >
>> > The current example shows the usage of looping in plpgsql, so as such
>> > there is no correctness issue, but OTOH there is no harm in updating
>> > the example as proposed by Ian Barwick.  Does anyone else see any
>> > problem with this idea?  If we agree to proceed with this update, it
>> > might be better to backpatch it for the sake of consistency though I
>> > am not sure about that.
>> >
>>
>> While checking the patch in back-branches, I noticed that it doesn't
>> get applied to 9.4 due to the way the example forms the string.  I
>> have done the required changes for 9.4 as well and attached is the
>> result.
>>
>
> Is question if for this queries should not be used some from information_schema instead direct access to pg_catalog.
>
> But I looked now, and we don't see materialized views in information_schema - what is probably bug.
>

I think you got the answer of this on a related thread.  Do you see
any other problems or have any concerns about this?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

Re: doc: update PL/pgSQL sample loop function

Pavel Stehule


st 11. 9. 2019 v 11:51 odesílatel Amit Kapila <[hidden email]> napsal:
On Wed, Sep 11, 2019 at 11:40 AM Pavel Stehule <[hidden email]> wrote:
>
> st 11. 9. 2019 v 7:45 odesílatel Amit Kapila <[hidden email]> napsal:
>>
>> On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <[hidden email]> wrote:
>> >
>> > The current example shows the usage of looping in plpgsql, so as such
>> > there is no correctness issue, but OTOH there is no harm in updating
>> > the example as proposed by Ian Barwick.  Does anyone else see any
>> > problem with this idea?  If we agree to proceed with this update, it
>> > might be better to backpatch it for the sake of consistency though I
>> > am not sure about that.
>> >
>>
>> While checking the patch in back-branches, I noticed that it doesn't
>> get applied to 9.4 due to the way the example forms the string.  I
>> have done the required changes for 9.4 as well and attached is the
>> result.
>>
>
> Is question if for this queries should not be used some from information_schema instead direct access to pg_catalog.
>
> But I looked now, and we don't see materialized views in information_schema - what is probably bug.
>

I think you got the answer of this on a related thread.  Do you see
any other problems or have any concerns about this?

no

Pavel


--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Reply | Threaded
Open this post in threaded view
|

Re: doc: update PL/pgSQL sample loop function

Ian Barwick-3
In reply to this post by akapila
On 2019/09/11 14:44, Amit Kapila wrote:

> On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <[hidden email]> wrote:
>>
>> The current example shows the usage of looping in plpgsql, so as such
>> there is no correctness issue, but OTOH there is no harm in updating
>> the example as proposed by Ian Barwick.  Does anyone else see any
>> problem with this idea?  If we agree to proceed with this update, it
>> might be better to backpatch it for the sake of consistency though I
>> am not sure about that.
>>
>
> While checking the patch in back-branches, I noticed that it doesn't
> get applied to 9.4 due to the way the example forms the string.  I
> have done the required changes for 9.4 as well and attached is the
> result.

Aha, I had it in my head that 9.4 was being deprecated soon and didn't
check that far back, but turns out it's around until Feb. 2020.

> Ian, if possible, can you once check the patch for 9.4?

Looks good, thanks for catching that!


Regards

Ian Barwick

--
  Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services