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

2017-04-09 Thread David E. Wheeler 
On Apr 9, 2017, at 9:59 PM, Andrew Gierth  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



smime.p7s
Description: S/MIME cryptographic signature

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

2017-04-09 Thread Andrew Gierth 
> "David" == David E Wheeler  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

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

2017-04-09 Thread David E. Wheeler 
On Apr 9, 2017, at 9:52 PM, Andrew Gierth  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



smime.p7s
Description: S/MIME cryptographic signature

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

2017-04-09 Thread Andrew Gierth 
> "David" == David E Wheeler  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

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

2017-04-09 Thread Tom Lane 
"David E. Wheeler"  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

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

2017-04-09 Thread David E. Wheeler 
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



smime.p7s
Description: S/MIME cryptographic signature