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;
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)
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)
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)
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)
-- 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)
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)
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)