Обсуждение: doc: update PL/pgSQL sample loop function

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

On Thu, Aug 29, 2019 at 10:07 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> čt 29. 8. 2019 v 5:03 odesílatel Ian Barwick <ian.barwick@2ndquadrant.com> 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

On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com> 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

On Wed, Sep 11, 2019 at 11:40 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> st 11. 9. 2019 v 7:45 odesílatel Amit Kapila <amit.kapila16@gmail.com> napsal:
>>
>> On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com> 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

On 2019/09/11 14:44, Amit Kapila wrote:
> On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com> 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