Re: [PATCH] regexp_positions ( string text, pattern text, flags text ) → setof int4range[]

út 9. 3. 2021 v 7:57 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> On Mon, Mar 8, 2021, at 21:46, Pavel Stehule wrote:
>
> so what about?
>
> CREATE OR REPLACE FUNCTION unnest_slice(anyarray, int)
> RETURNS SETOF anyarray AS $$
> DECLARE r $1%type;
> BEGIN
> FOREACH r SLICE $2 IN ARRAY $1 --- now $2 should be constant
> LOOP
> RETURN NEXT r;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>
>
> Not sure I understand. Is the suggestion to add "SLICE" as syntactic sugar
> in PL/pgSQL to invoke the proposed two-argument C-version of unnest()?
>

there are two ideas:

1. the behaviour can be same like SLICE clause of FOREACH statement

2. use unnest_slice as name - the function "unnest" is relatively rich
today and using other overloading doesn't look too practical. But this is
just an idea. I can imagine more forms of slicing or unnesting, so it can
be practical to use different names than just "unnest".

Personally I don't like too much using 2D arrays for this purpose. The
queries over this functionality will be harder to read (it is like fortran
77). I understand so now, there is no other possibility, because pg cannot
build array type from function signature. So it is harder to build an array
of record types.

We can make an easy tuple store of records - like FUNCTION fx(OUT a int,
OUT b int) RETURNS SETOF RECORD. But now, thanks to Tom and Amit's work,
the simple expression evaluation is significantly faster than SQL
evaluation. So using any SRF function has performance impact. What I miss
is the possibility to write functions like FUNCTION fx(OUT a int, OUT b
int) RETURNS ARRAY. With this possibility is easy to write functions that
you need, and is not necessary to use 2d arrays. If the result of regexp
functions will be arrays of records, then a new unnest function is not
necessary. So this is not a good direction. Instead of fixing core issues,
we design workarounds. There can be more wide usage of arrays of composites.

Regards

Pavel

> /Joel
>
>
>