Re: Allow any[] as input arguments for sql/plpgsql functions to mimic format()

=?UTF-8?Q?Micha=c5=82_=22phoe=22_Herda?= <phoe(at)disroot(dot)org> writes:
> My reasoning in this case is - if we allow the any[] type to only be
> passed to other functions that accept any[], and disallow any kind of
> other operations on this array (such as retrieving its elements or
> modifying it), I do not yet see any places where it might introduce a
> performance regression.

Performance regressions are not the question here --- or at least, there
are a lot of other questions to get past first.

* plpgsql doesn't have any mechanism for restricting the use of a
parameter in the way you suggest. It's not clear if it'd be practical
to add one, given the arms-length way in which plpgsql does expression
evaluation, and it seems likely that any such thing would be messy
and bug-prone.

* There's not actually any such type as any[]. There's anyarray,
which is not what you're wishing for here because it'd constrain
all the actual arguments to be the same type (or at least coercible
to the same array element type). This is related to the next point...

* format() isn't declared as taking any[]. It's really

regression=# \df format
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+----------------------+------
pg_catalog | format | text | text | func
pg_catalog | format | text | text, VARIADIC "any" | func
(2 rows)

"VARIADIC any" is a very special hack, because unlike other VARIADIC
cases, it doesn't result in collapsing the actual arguments into an
array. (Again, it can't because they might not have a common type.)
The called function has to have special logic for examining its
arguments to find out how many there are and what their types are.
format() can do that because it's written in C, but a plpgsql function,
not so much.

* We could imagine allowing a plpgsql function to be declared
"VARIADIC any", and treating it as having N polymorphic arguments of
independent types, but then what? plpgsql has no notation for
accessing such arguments (they wouldn't have names, to start with),
nor for finding out how many there are, and it certainly has no
notation for passing the whole group of them on to some other function.

I think the closest you'll be able to get here is to declare the
plpgsql function as taking "variadic text[]" and then passing the
text array to format() with a VARIADIC marker. That should work
mostly okay, although calls might need explicit casts to text
in some cases.

FWIW, it'd likely be easier to get around these problems in plperl
or pltcl than in plpgsql, as those are both much less concerned
with the exact data types of their arguments than plpgsql, and
more accustomed to dealing with functions with variable argument
lists. I don't know Python well enough to say whether the same
is true of plpython.

regards, tom lane