Wednesday, April 21, 2010

two small functions for arrays

Hello, If you miss some functions for arrays look here:
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)

3 Comments:

At August 18, 2010 at 11:38 PM , Anonymous yon said...

Hey, could You explain to me how should I interpret "unnest($1) g(v)" shouldnt there be an ',' ? Otherwise I've never seen sucha a construct in psql and I wonder how it works.

 
At August 18, 2010 at 11:45 PM , Blogger Pavel Stěhule said...

this is just alias with column names. The "unnest" is table function - it returns a table, so I can rename a returned table (by g like generator) and columns (by v like value).

it is same like

postgres=# create table a(b integer);
CREATE TABLE
Time: 173.711 ms
postgres=# select * from a g(x);
x
───
(0 rows)

 
At August 19, 2010 at 10:43 PM , Anonymous yon said...

Thank You, now it is all clear. Btw, great blog with very interesting posts. I hope You will keep it that way :)

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home