Prev: [HACKERS] buildfarm building all live branches from git
Next: what is good solution for support NULL inside string_to_arrayfunction?
From: Pavel Stehule on 3 May 2010 18:05
Hello

I understand why we don't support expression 'null'::sometype. But it
does problems with array deserialisation.

postgres=# select array_to_string(ARRAY[10,20,30,NULL,30], '|');
array_to_string
-----------------
10|20|30|30
(1 row)

quietly removing NULL is maybe good for compatibility but is wrong for
functionality. Can we enhance function array_to_string and
string_to_array like:

CREATE OR REPLACE FUNCTION array_to_string(dta anyarray, sep text,
nullsym text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT coalesce(v::text,$3)
FROM unnest($1) g(v)),$2)
$$ LANGUAGE sql;
CREATE FUNCTION
Time: 231.445 ms
postgres=# select array_to_string(ARRAY[10,20,30,NULL,30], '|', '');
array_to_string
-----------------
10|20|30||30
(1 row)

Time: 230.879 ms
postgres=# select array_to_string(ARRAY[10,20,30,NULL,30], '|', 'NULL');
array_to_string
------------------
10|20|30|NULL|30
(1 row)

Time: 2.031 ms

CREATE OR REPLACE FUNCTION string_to_array(str text, sep text, nullsym text)
RETURNS text[] AS $$
SELECT ARRAY(SELECT CASE WHEN v <> $3 THEN v ELSE NULL END
FROM unnest(string_to_array($1,$2)) g(v))
$$ LANGUAGE sql;
CREATE FUNCTION
Time: 29.044 ms

postgres=# SELECT string_to_array('10,20,30,,40',',','');
string_to_array
--------------------
{10,20,30,NULL,40}
(1 row)

postgres=# SELECT string_to_array('10,20,30,,40',',','')::int[];
string_to_array
--------------------
{10,20,30,NULL,40}
(1 row)

it is correct?

other ideas?

Regards
Pavel Stehule

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 | 
Pages: 1
Prev: [HACKERS] buildfarm building all live branches from git
Next: what is good solution for support NULL inside string_to_arrayfunction?