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/pgSQL function 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 array doesn’t work in an exception-handling block.
Best,
David
Attachments:
"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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"David" == David E Wheeler <david@justatheory.com> writes:
David> And it works great, including in PL/pgSQL functions, except in
David> an exception block. When I run this:
David> BEGIN;
David> CREATE OR REPLACE FUNCTION foo(
David> ) RETURNS BOOLEAN IMMUTABLE LANGUAGE PLPGSQL AS $$
David> DECLARE
David> things TEXT[] := '{}';
David> BEGIN
David> things := things || 'foo';
David> RAISE division_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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
"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 array literal: "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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 without casting ‘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 returns a ::text, so no ambiguity. Guess that’s my issue.
Thanks,
David