Re: POC: PLpgSQL FOREACH IN JSON ARRAY

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,)
}

Regards

Pavel

> regards, tom lane
>