Array based functions

• dynamic record update

CREATE OR REPLACE FUNCTION public.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
 _list text;

BEGIN
_list := (
   SELECT string_agg(x.fld, ',')
   FROM   (
      SELECT CASE WHEN a.attname = $2
              THEN quote_literal($3)
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a 
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_type
                           WHERE  oid = pg_typeof($1)::oid) 
      ORDER BY a.attnum
   ) x
);

EXECUTE '
SELECT ' || _list || '
FROM   (SELECT $1.*) x'
USING  $1
INTO   $1;

RETURN $1;
END;
$body$ LANGUAGE plpgsql;

• array sort

CREATE OR REPLACE FUNCTION array_sort(anyarray)
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT unnest($1) ORDER BY 1)
$$ LANGUAGE sql;

postgres=# select array_sort(array[1,2,34,1,2]);
  array_sort  
--------------
 {1,1,2,2,34}
(1 row)

• remove duplicit values

CREATE OR REPLACE FUNCTION array_distinct(anyarray)
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT DISTINCT unnest($1))
$$ LANGUAGE sql;

postgres=# select array_distinct(array[1,2,3,21,1,2,1]);
 array_distinct 
----------------
 {1,2,21,3}
(1 row)

• append distinct value

CREATE OR REPLACE FUNCTION array_append_distinct(anyarray, anyelement) 
RETURNS anyarray AS $$ 
  SELECT ARRAY(SELECT unnest($1) union SELECT $2) 
$$ LANGUAGE sql;

postgres=# select array_append_distinct(array[1,2,3,4],1);
 array_append_distinct 
-----------------------
 {1,2,3,4}
(1 row)

postgres=# select array_append_distinct(array[1,2,3,4],7);
 array_append_distinct 
-----------------------
 {1,2,3,4,7}
(1 row)

• drop value from array

CREATE OR REPLACE FUNCTION array_erase(anyarray, anyelement) 
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT v FROM unnest($1) g(v) WHERE v <> $2)
$$ LANGUAGE sql;

postgres=# select array_erase(array[1,2,3,2,5], 2);
 array_erase 
-------------
 {1,3,5}
(1 row)

postgres=# select array_erase(array[1,2,3,2,5], 1);
 array_erase 
-------------
 {2,3,2,5}
(1 row)

• strict greatest

-- standard greates
postgres=# select greatest(10,null,11);
 greatest 
----------
       11
(1 row)

CREATE OR REPLACE FUNCTION greatest_strict(VARIADIC anyarray) 
RETURNS anyelement AS $$
  SELECT unnest($1) 
    ORDER BY 1 DESC NULLS FIRST 
    LIMIT 1 
$$ LANGUAGE sql;

postgres=# select greatest_strict(10,1,11); greatest_strict 
-----------------
              11
(1 row)

postgres=# select greatest_strict(10,1,11, NULL);
 greatest_strict 
-----------------
                
(1 row)

• Position of item in array

CREATE OR REPLACE FUNCTION indexof(anyarray, anyelement) 
RETURNS int AS $$ 
  SELECT i 
     FROM generate_subscripts($1,1) g(i) 
    WHERE $1[i] = $2 
    LIMIT 1
$$ LANGUAGE sql;

postgres=# select indexof(array[1,2,3,1], 3); indexof 
---------
       3
(1 row)

• intersect/ of two arrays

CREATE OR REPLACE FUNCTION array_intersect(anyarray, anyarray) 
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT unnest($1) 
               INTERSECT 
               SELECT unnest($2))
$$ LANGUAGE sql;

postgres=# select array_intersect(array[1,2,3],array[2,3,4]);
 array_intersect 
-----------------
 {2,3}
(1 row)

• array subtraction

CREATE OR REPLACE FUNCTION array_subtraction(anyarray, anyarray) 
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT unnest($1) 
               EXCEPT 
               SELECT unnest($2))
$$ LANGUAGE sql;

postgres=# select array_subtraction(array[1,2,3],array[2,3,4]);
 array_subtraction 
-------------------
 {1}
(1 row)

• to_string, to_array functions - string <-> array functions with well NULL support

Hello

I like function string_to_array and array_to_string. The power of these functions is unlimited. Last week somebody noticed problems with NULLs related to string_to_array function. It is true - these function doesn't handle NULL well :(.

postgres=# SELECT string_to_array('1,2,3,NULL,5',',')::int[];
ERROR:  invalid input syntax for integer: "NULL"

I would to prepare new version of these function (with better support of NULL) - functions to_array and to_string. It can be prepared for 9.1.

CREATE OR REPLACE FUNCTION to_string(anyarray, sep text, nullstr text DEFAULT '') 
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT coalesce(v::text, $3) 
                                FROM unnest($1) g(v)),
                       $2)
$$ LANGUAGE sql;

postgres=# select to_string(array[1,2,3,4,null,5],','); 
 to_string  
------------
 1,2,3,4,,5
(1 row)

postgres=# select to_string(array[1,2,3,4,null,5],',','<NULL>');
    to_string     
------------------
 1,2,3,4,<NULL>,5
(1 row)

CREATE OR REPLACE FUNCTION to_array(text, sep text, nullstr text DEFAULT '') 
RETURNS text[] AS $$ 
  SELECT ARRAY(SELECT CASE 
                           WHEN v = $3 THEN NULL::text 
                           ELSE v END 
                  FROM unnest(string_to_array($1,$2)) g(v)) 
$$ LANGUAGE sql;

postgres=# select to_array('1,2,3,4,,5',',');
     to_array     
------------------
 {1,2,3,4,NULL,5}
(1 row)

postgres=# select to_array('1,2,3,4,<NULL>,5',',','<NULL>');
     to_array     
------------------
 {1,2,3,4,NULL,5}
(1 row)

postgres=# select to_array('1,2,3,,5',',')::int[];
    to_array    
----------------
 {1,2,3,NULL,5}
(1 row)