what is good solution for support NULL inside string_to_array function?

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: what is good solution for support NULL inside string_to_array function?
Date: 2010-05-03 22:05:39
Message-ID: AANLkTinzwMEPss-SO-w3p6AoQ23hBdcuiMQAeXSUc_xd@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2010-05-04 00:55:06 Re: what is good solution for support NULL inside string_to_array function?
Previous Message Josh Berkus 2010-05-03 22:04:32 Re: max_standby_delay considered harmful