Thread: [HACKERS] Malformed Array Literal in PL/pgSQL Exception Block

Hackers,

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. When I run this:
   BEGIN;
   CREATE OR REPLACE FUNCTION foo(   ) RETURNS BOOLEAN IMMUTABLE LANGUAGE PLPGSQL AS $$   DECLARE       things TEXT[]
:='{}';   BEGIN       things := things || 'foo';       RAISE division_by_zero;   EXCEPTION WHEN OTHERS THEN
things:= things || 'bar';   END;   $$; 
   SELECT foo();
   ROLLBACK;

The output is:
   psql:array.sql:15: ERROR:  malformed array literal: "bar"   LINE 1: SELECT things || 'bar'
^   DETAIL:  Array value must start with "{" or dimension information.   QUERY:  SELECT things || 'bar'   CONTEXT:
PL/pgSQLfunction foo() line 8 at assignment 

Note that it’s fine with the use of || outside the exception block, but not inside! I’ve worked around this by using
`things|| '{bar}'` instead, but it seems like a bug or perhaps unforeseen corner case that appending a value to an
arraydoesn’t work in an exception-handling block. 

Best,

David

"David E. Wheeler" <david@justatheory.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

>>>>> "David" == David E Wheeler <david@justatheory.com> writes:
David> And it works great, including in PL/pgSQL functions, except inDavid> an exception block. When I run this:
David>     BEGIN;
David>     CREATE OR REPLACE FUNCTION foo(David>     ) RETURNS BOOLEAN IMMUTABLE LANGUAGE PLPGSQL AS $$David>
DECLAREDavid>        things TEXT[] := '{}';David>     BEGINDavid>         things := things || 'foo';David>
RAISEdivision_by_zero;
 

This "raise" statement is not reached, because the previous line raises
the "malformed array literal" error.
David>     EXCEPTION WHEN OTHERS THEN

If you change this to  EXCEPTION WHEN division_by_zero THEN, the
reported error becomes:

ERROR:  malformed array literal: "foo"
LINE 1: SELECT things || 'foo'

-- 
Andrew (irc:RhodiumToad)

On Apr 9, 2017, at 9:52 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

> This "raise" statement is not reached, because the previous line raises
> the "malformed array literal" error.

Bah!

> David>     EXCEPTION WHEN OTHERS THEN
>
> If you change this to  EXCEPTION WHEN division_by_zero THEN, the
> reported error becomes:
>
> ERROR:  malformed array literal: "foo"
> LINE 1: SELECT things || 'foo'

So the issue stands, yes?

D

>>>>> "David" == David E Wheeler <david@justatheory.com> writes:
>> If you change this to EXCEPTION WHEN division_by_zero THEN, the>> reported error becomes:>> >> ERROR:  malformed
arrayliteral: "foo">> LINE 1: SELECT things || 'foo'
 
David> So the issue stands, yes?

Tom's response has the explanation of why it fails (everywhere, not just
in the exception block): parse analysis prefers to match the (array ||
array) form of the operator when given input of (array || unknown). Just
cast the 'foo' to the array element type.

-- 
Andrew (irc:RhodiumToad)

On Apr 9, 2017, at 9:59 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

> Tom's response has the explanation of why it fails (everywhere, not just
> in the exception block): parse analysis prefers to match the (array ||
> array) form of the operator when given input of (array || unknown). Just
> cast the 'foo' to the array element type.

Tried to reduce this from some code I’m working on. I have a whole bunch of code that appends to an array in this way
withoutcasting ‘foo’ to text or text[]. It’s only in an exception block that it’s complaining. 

Hrm, looking back through my code, it looks like I’m mostly calling format() to append to an array, which of course
returnsa ::text, so no ambiguity. Guess that’s my issue. 

Thanks,

David