Re: Malformed Array Literal in PL/pgSQL Exception Block

"David E. Wheeler" <david(at)justatheory(dot)com> writes:
> I’ve been happily using the array-to-element concatenation operator || to append a single value to an array, e.g,
> SELECT array || 'foo';
> And it works great, including in PL/pgSQL functions, except in an
> exception block.

Hm, really?

regression=# create table zit (things text[]);
CREATE TABLE
regression=# insert into zit values(array['foo','bar']);
INSERT 0 1
regression=# select things || 'baz' from zit;
ERROR: malformed array literal: "baz"
LINE 1: select things || 'baz' from zit;
^
DETAIL: Array value must start with "{" or dimension information.

I think the problem here is that without any other info about the
type of the right-hand argument of the || operator, the parser will
assume that it's the same type as the left-hand argument; which
is not unreasonable, because there is an array || array operator.

If you are more specific about the type of the RHS then it's fine:

regression=# select things || 'baz'::text from zit;
?column?
---------------
{foo,bar,baz}
(1 row)

> Note that it’s fine with the use of || outside the exception block, but
> not inside!

Don't see why an exception block would have anything to do with it.

regards, tom lane