Skip to content

Instantly share code, notes, and snippets.

@silas

silas/array_helpers.sql

Last active January 13, 2022 20:50
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save silas/cf9f14b6b46a19a49ad37f43372d0c20 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save silas/cf9f14b6b46a19a49ad37f43372d0c20 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. silas revised this gist Jan 13, 2022. 2 changed files with 12 additions and 4 deletions.
    4 changes: 0 additions & 4 deletions array_distinct.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +0,0 @@
    CREATE OR REPLACE FUNCTION array_distinct(anyarray) RETURNS anyarray AS $$
    SELECT coalesce(array_agg(x.v), '{}')
    FROM (SELECT DISTINCT unnest($1) as v ORDER BY 1) x
    $$ LANGUAGE SQL
    12 changes: 12 additions & 0 deletions array_helpers.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,12 @@
    CREATE OR REPLACE FUNCTION array_distinct(anyarray) RETURNS anyarray AS $$
    SELECT coalesce(array_agg(x.value ORDER BY x.pos), '{}')
    FROM (
    SELECT DISTINCT ON (item.value) item.value, item.pos
    FROM unnest($1) WITH ORDINALITY item(value, pos)
    ) x
    $$ LANGUAGE SQL

    CREATE OR REPLACE FUNCTION array_set(anyarray) RETURNS anyarray AS $$
    SELECT coalesce(array_agg(x.v), '{}')
    FROM (SELECT DISTINCT unnest($1) AS v ORDER BY 1) x
    $$ LANGUAGE SQL
  2. silas created this gist Jan 4, 2022.
    4 changes: 4 additions & 0 deletions array_distinct.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,4 @@
    CREATE OR REPLACE FUNCTION array_distinct(anyarray) RETURNS anyarray AS $$
    SELECT coalesce(array_agg(x.v), '{}')
    FROM (SELECT DISTINCT unnest($1) as v ORDER BY 1) x
    $$ LANGUAGE SQL