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)
Pages
▼
Wednesday, April 21, 2010
two small functions for arrays
Hello,
If you miss some functions for arrays look here:
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.
ReplyDeletethis 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).
ReplyDeleteit is same like
postgres=# create table a(b integer);
CREATE TABLE
Time: 173.711 ms
postgres=# select * from a g(x);
x
───
(0 rows)
Thank You, now it is all clear. Btw, great blog with very interesting posts. I hope You will keep it that way :)
ReplyDelete