From 6fc87b6c36c598f6b494b0869f9b90b4bb90a24f Mon Sep 17 00:00:00 2001
From: glyn <glyn@8kb.co.uk>
Date: Mon, 15 Dec 2014 21:55:50 +0000
Subject: [PATCH 1/1] Initial commit

---
 .gitignore                        |  2 ++
 README.md                         | 31 +++++++++++++++++
 pg_array_agg_mult.sql             | 16 +++++++++
 pg_two_dimensional_unnest.plpgsql | 56 +++++++++++++++++++++++++++++++
 4 files changed, 105 insertions(+)
 create mode 100644 .gitignore
 create mode 100644 README.md
 create mode 100644 pg_array_agg_mult.sql
 create mode 100644 pg_two_dimensional_unnest.plpgsql

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..ff37810
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,2 @@
+.DS_Store
+Thumb.db
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..7d154d0
--- /dev/null
+++ b/README.md
@@ -0,0 +1,31 @@
+2d_arrays
+==========
+
+Just a couple of functions and an aggregate for working with multidimensional (2 dimensional) arrays in PostgreSQL.
+
+Usage
+-----
+
+```sql
+TEST=# select unnest_element(ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]],3);
+ unnest_element
+----------------
+              3
+              6
+(2 rows)
+
+TEST=# select unnest_first(ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]]);
+ unnest_dimension
+------------------
+ {1,2,3}
+ {4,5,6}
+(2 rows)
+
+-- (Ref http://stackoverflow.com/questions/9832973/initial-array-in-function-to-aggregate-multi-dimensional-array)
+TEST=# SELECT array_agg_mult(ARRAY[ARRAY[a,b]]) 
+       FROM (SELECT 1 AS a, 2 AS b UNION SELECT 2 AS a, 4 AS b) t;
+ array_agg_mult
+----------------
+ {{1,2},{2,4}}
+(1 row) 
+```
diff --git a/pg_array_agg_mult.sql b/pg_array_agg_mult.sql
new file mode 100644
index 0000000..66c8c54
--- /dev/null
+++ b/pg_array_agg_mult.sql
@@ -0,0 +1,16 @@
+-- To aggregate rows into 2 dimensional arrays see the following custom aggregate by Erwin Brandstetter
+-- http://stackoverflow.com/questions/9832973/initial-array-in-function-to-aggregate-multi-dimensional-array
+
+CREATE AGGREGATE public.array_agg_mult (anyarray)  (
+    SFUNC     = array_cat
+   ,STYPE     = anyarray
+   ,INITCOND  = '{}'
+);
+
+-- E.g.
+-- TEST=# SELECT array_agg_mult(ARRAY[ARRAY[a,b]]) 
+--        FROM (SELECT 1 AS a, 2 AS b UNION SELECT 2 AS a, 4 AS b) t;
+--  array_agg_mult
+-- ----------------
+--  {{1,2},{2,4}}
+-- (1 row)       
diff --git a/pg_two_dimensional_unnest.plpgsql b/pg_two_dimensional_unnest.plpgsql
new file mode 100644
index 0000000..747bc21
--- /dev/null
+++ b/pg_two_dimensional_unnest.plpgsql
@@ -0,0 +1,56 @@
+-- 
+-- Glyn Astill 21/04/2012
+-- Functions to assist unnesting multidimensional arrays in PostgreSQL 9.0 and earlier
+--
+
+-- Some similar functions for Pg 9.1+ only can be found here
+-- https://wiki.postgresql.org/wiki/Unnest_multidimensional_array
+-- https://wiki.postgresql.org/wiki/Multidimensional_Array_Mapping
+
+
+-- Given a 2 dimensional array will unnest the element at the supplied
+-- index in the second dimension.
+-- Pg 8.0+
+CREATE OR REPLACE FUNCTION public.unnest_element(anyarray, integer)
+RETURNS SETOF anyelement
+AS $BODY$
+BEGIN
+	RETURN QUERY SELECT $1[i][$2]
+	FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
+END;
+$BODY$
+LANGUAGE plpgsql IMMUTABLE;
+
+--
+
+-- Given a 2 dimensional array will unnest the first dimension
+-- returning the a set of one dimensional array.
+-- Pg 8.4+
+CREATE OR REPLACE FUNCTION public.unnest_first(anyarray)
+RETURNS SETOF anyarray
+AS $BODY$
+BEGIN
+	RETURN QUERY 
+	SELECT (SELECT array_agg($1[i][i2])
+		FROM generate_series(array_lower($1,2), array_upper($1,2)) i2)
+	FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
+END;
+$BODY$
+LANGUAGE plpgsql IMMUTABLE;
+
+
+-- E.g.
+-- 
+-- TEST=# select unnest_element(ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]],3);
+--  unnest_element
+-- ----------------
+--               3
+--               6
+-- (2 rows)
+-- 
+-- TEST=# select unnest_first(ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]]);
+--  unnest_dimension
+-- ------------------
+--  {1,2,3}
+--  {4,5,6}
+-- (2 rows)
-- 
2.39.2