Re: POC: PLpgSQL FOREACH IN JSON ARRAY

Hi

čt 12. 3. 2026 v 8:00 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

> Hi
>
> čt 12. 3. 2026 v 5:30 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
>> Peter Eisentraut <peter@eisentraut.org> writes:
>> > Maybe this could be written in such a way that it doesn't hardcode JSON
>> > arrays specifically, but a type could have an iteration helper function
>> > that would feed this feature?
>>
>> +1.  ISTM that this feature would make sense for subscriptable types,
>> so one way to shoehorn it into the system without a lot of new overhead
>> could be to extend struct SubscriptRoutines to offer optional support
>> function(s) for iterating through all the elements of a subscriptable
>> object.
>>
>
>
attached patch do this - new interface has two
methods: CreateForeachAIterator and iterate

diff --git a/src/include/nodes/subscripting.h
b/src/include/nodes/subscripting.h
index 301f21dac2f..08bfe59ede4 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -154,6 +154,32 @@ typedef void (*SubscriptExecSetup) (const
SubscriptingRef *sbsref,
                                    SubscriptingRefState *sbsrefstate,
                                    SubscriptExecSteps *methods);

+typedef struct _ForeachAIterator ForeachAIterator;
+
+/*
+ * ForeachAIiterator is used by PLpgSQL FOREACH IN ARRAY statement.
+ * Input value should not be null, and inside CreateForeachAIterator
+ * routine must be copied to current (statement) context. "iterate"
+ * routine is called under short life memory context, that is resetted
+ * after any call.
+ */
+struct _ForeachAIterator
+{
+   bool        (*iterate) (ForeachAIterator *self,
+                           Datum *value,
+                           bool *isnull,
+                           Oid *typid,
+                           int32 *typmod);
+   /* Private fields might appear beyond this point... */
+};
+
+typedef ForeachAIterator * (*CreateForeachAIterator) (Datum value,
+                                                     Oid typid,
+                                                     int32 typmod,
+                                                     int slice,
+                                                     Oid target_typid,
+                                                     int32 target_typmod);
+
 /* Struct returned by the SQL-visible subscript handler function */
 typedef struct SubscriptRoutines
 {
@@ -163,6 +189,9 @@ typedef struct SubscriptRoutines
    bool        fetch_leakproof;    /* is fetch SubscriptingRef leakproof?
*/
    bool        store_leakproof;    /* is assignment SubscriptingRef
                                     * leakproof? */
+
+   /* returns iterator used by PL/pgSQL FOREACH statement */
+   CreateForeachAIterator create_foreach_a_iterator;
 } SubscriptRoutines;

 #endif                         /* SUBSCRIPTING_H */

Regards

Pavel


>
>

> Regards
>
> Pavel
>
>
>>
>>                         regards, tom lane
>>
>