Обсуждение: surprised by non-strict array_append
I was caught out today by the non-strict behavior of array_append causing me to get an undesired result for a COALESCE. My subsequent attempt to create a STRICT VARIADIC generalization of array_append led to another surprise. The problem was easily solved, but might be of interest to others. Perhaps someone will enlighten me as to why the standard array functions are non-strict and why the STRICT VARIADIC function fails to be strict. I've boiled it down to make it clear: psql (8.4.2) SELECT COALESCE( ARRAY[1] || NULL::integer, ARRAY[42] ); coalesce ---------- {1,NULL} (1 row) SELECT COALESCE( array_append( ARRAY[1], NULL), ARRAY[42] ); coalesce ---------- {1,NULL} (1 row) CREATE OR REPLACE FUNCTION array_add(ANYARRAY, VARIADIC ANYARRAY) RETURNS ANYARRAY AS $$ SELECT $1 || $2 $$ LANGUAGE sql STRICT; COMMENT ON FUNCTION array_add(ANYARRAY, ANYARRAY) IS 'a strict generalization version of array_append'; SELECT COALESCE( array_add( ARRAY[1], NULL, NULL), ARRAY[42] ); coalesce --------------- {1,NULL,NULL} (1 row) -- hmm - not very strict! CREATE OR REPLACE FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$ SELECT $1 || $2 $$ LANGUAGE sql STRICT; COMMENT ON FUNCTION array_add1(ANYARRAY, ANYELEMENT) IS 'a strict version of array_append'; SELECT COALESCE( array_add1( ARRAY[1], NULL), ARRAY[42] ); coalesce ---------- {42} (1 row) -- ah, finally! _Greg
If it were strict wouldn't it return NULL?
greg
On 3 Feb 2010 07:16, "J. Greg Davidson" <jgd@well.com> wrote:
I was caught out today by the non-strict behavior of array_append
causing me to get an undesired result for a COALESCE. My subsequent
attempt to create a STRICT VARIADIC generalization of array_append
led to another surprise. The problem was easily solved, but might
be of interest to others. Perhaps someone will enlighten me as to
why the standard array functions are non-strict and why the STRICT
VARIADIC function fails to be strict. I've boiled it down to make
it clear:
psql (8.4.2)
SELECT COALESCE( ARRAY[1] || NULL::integer, ARRAY[42] );
coalesce
----------
{1,NULL}
(1 row)
SELECT COALESCE( array_append( ARRAY[1], NULL), ARRAY[42] );
coalesce
----------
{1,NULL}
(1 row)
CREATE OR REPLACE
FUNCTION array_add(ANYARRAY, VARIADIC ANYARRAY) RETURNS ANYARRAY AS $$
SELECT $1 || $2
$$ LANGUAGE sql STRICT;
COMMENT ON FUNCTION array_add(ANYARRAY, ANYARRAY)
IS 'a strict generalization version of array_append';
SELECT COALESCE( array_add( ARRAY[1], NULL, NULL), ARRAY[42] );
coalesce
---------------
{1,NULL,NULL}
(1 row)
-- hmm - not very strict!
CREATE OR REPLACE
FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$
SELECT $1 || $2
$$ LANGUAGE sql STRICT;
COMMENT ON FUNCTION array_add1(ANYARRAY, ANYELEMENT)
IS 'a strict version of array_append';
SELECT COALESCE( array_add1( ARRAY[1], NULL), ARRAY[42] );
coalesce
----------
{42}
(1 row)
-- ah, finally!
_Greg
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general