Re: POC: PLpgSQL FOREACH IN JSON ARRAY

po 27. 7. 2026 v 9:02 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
> čt 23. 7. 2026 v 20:07 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> >
> > Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > > st 1. 7. 2026 v 23:40 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> > >> * it's not clear to me that these routines have any business knowing
> > >> about the "target type"; they certainly shouldn't contain comments
> > >> alluding to PLpgSQL's conversion abilities. I think probably you
> > >> just want them to pass back the data type they are producing and
> > >> let PLpgSQL decide whether it wants to convert or not.
> >
> > > The iterator should know the target type for more reasons:
> >
> > I wasn't super convinced by these arguments, so today I tried to
> > benchmark the performance question by asking Claude Code to do
> > that. It came up with some interesting results, notably that this
> > only matters for composite types not scalars. It also suggested
> > that we could make the API cleaner with minimal performance
> > loss by inventing a separate "coerce()" callback. As presented,
> > its patch is a mess because it chose to allow run-time selection
> > among several different ways, which of course we wouldn't do.
> > But anyway, take a look at that option and see if it makes sense
> > to you.
> >
> > (I should note that the benchmarking was done in a VM on my laptop.
> > So it's hardly an industrial-strength setup, but since we only
> > care about single-threaded performance here, I think the results
> > are probably reasonably trustworthy. Feel free to reproduce for
> > yourself though.)
> >
>
> I am thinking about possibilities on how to design this API. The main
> problem is in fact so container type like jsonb can contain nested typed
> values (possibly labeled values), and then there is not only one valid
> possibility of how to iterate over an array. Second issue is overhead based
> on packing and unpacking value, that is necessary when iterate and coerce
> are distinct methods. Third issue is responsibility - usually we want to
> push all logic to the iterator. The usage of iterators can be different
> too. Inside PL/pgSQL I have complete environment for casting inside and I
> mostly prefer casting inside PL/pgSQL (from consistency reasons), outside
> PL/pgSQL users probably prefer manual casting or casting fully provided by
> iterator (with possibility to choose implicit or explicit casting).
>
> Can there be a solution with one optional argument (of iterate method),
> that can hold a setup?
>
> so API can looks like
>
> typedef enum ForeachAIteratorMode
> {
> USE_COMMON_TYPE,
> BASE_TYPES_WITHOUT_COERCION,
> BASE_TYPES_WITH_POSSIBLE_COERCION,
> FORCE_IMPLICIT_COERCION,
> FORCE_EXPLICIT_COERCION
> } ForeachAIteratorMode;
>
> typedef struct ForeachAIteratorOptions
> {
> Oid result_typid,
> int32 result_typmod,
> ForeachAIteratorMode mode
> } ForeachAIteratorOptions
>
> struct
> {
> bool (*iterate) (ForeachAIterator *self,
> Datum *value,
> Oid *typid,
> int32 *typmod,
> ForeachAIteratorOptions *options,)
> }
>
>
>
and patch

regards

Pavel

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