imported>Pavel v 16. 2. 2012, 04:51

2012-02-16T04:51:25Z

Nová stránka

[[category:Articles]]
* dynamic record update
<pre>
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;
</pre>

* array sort
<pre>
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)
</pre>
* remove duplicit values
<pre>
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)
</pre>
* append distinct value
<pre>
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)
</pre>
* drop value from array
<pre>
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)
</pre>
* strict greatest
<pre>
-- 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)
</pre>
* Position of item in array
<pre>
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)
</pre>
* intersect/ of two arrays
<pre>
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)
</pre>
* array subtraction
<pre>
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)
</pre>
* to_string, to_array functions - string <-> array functions with well NULL support
<pre>
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)
</pre>

Array based functions - Historie editací

imported>Pavel v 16. 2. 2012, 04:51