Re: POC: PLpgSQL FOREACH IN JSON ARRAY

Hi

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

> Hi
>
> čt 12. 3. 2026 v 5:30 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
>
>> Peter Eisentraut <peter(at)eisentraut(dot)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
>>
>