On Thu, Apr 2, 2009 at 12:10 PM, David E. Wheeler <david@kineticode.com> wrote:
> On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:
>
>>> my @ints = map { $_ || 0 } split ',', $string;
>>>
>>> This ensures that I get the proper number of records in the example of
>>> something like '1,2,,4'.
>>
>> I can't see that there's any way to do this in SQL regardless of how
>> we define this operation.
>
> It's easy enough to write a function to do it:
>
> CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
> SELECT ARRAY(
> SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE $1[i] END
> FROM generate_series(1, array_upper($1, 1)) s(i)
> ORDER BY i
> );
> $$ LANGUAGE SQL IMMUTABLE;
Ah! Thanks for the tip.
...Robert