How to dereference 2 dimensional array?

Hi,
this could be start point for discussion:

CREATE OR REPLACE FUNCTION public."arraysToHstore" (IN a TEXT[], OUT c
hstore[])
RETURNS hstore[]
AS
$BODY$
DECLARE
i INT;
elements INT;
dim INT;
BEGIN
elements := array_length(a,2); -- # of elements in each dimension
dim := array_length(a,1); -- # of dimensions
a := string_to_array(array_to_string(a, '|', 'NULL'), '|', 'NULL');
--rewrite multidimensional array into single dimensional

FOR i IN 0..(dim -1) LOOP --loop throug all dimensions to create hsore
array (0 is for first elment in rewrited array)
c[i+1] = hstore (a[(i*elements)+1: (i+1)*elements]); --create hstore array
element using part of array "a", this part (window) is moving while loop is
evaluated)
END LOOP;

END
$BODY$
LANGUAGE plpgsql SECURITY DEFINER IMMUTABLE STRICT;

--usage example
SELECT (public."arraysToHstore"('{{"f1", "1", "f3", "123", "f4", "ABC"},
{"f5", "2", "f6", "345", "f7", "DEF"}}')::hstore[])[2]

This works for me (PostgreSQL 9.1.2).
You can pass whatever text array You want (any size, but 2 dimensions only)
and You will receive 1 dimentional hstore array with number of elements
corresponding to input array's dimensions)

Regards,
Bartek

2012/2/16 ChoonSoo Park <luispark@gmail.com>

On Thu, Feb 16, 2012 at 9:48 AM, ChoonSoo Park <luispark@gmail.com> wrote:

I would like to construct hstore array from 2 dimensional array.

For example,

'{{f1, 1, f2, 123, f3, ABC, ...}, {f1, 2, f2, 345, f3, DEF, ...}}'

Should be converted to

2 hstore values

"f1"=>"1", "f2"=>"123", "f3"=>"ABC", ...

"f2"=>"2", "f2"=>"345", "f3"=>"DEF", ...

create or replace function convertHStore(p1 text[][]) RETURNS hstore[]

hstore function requires text[] to convert array to hstore. Therefore I
should be able to dereference 2 dimensional array element.

Inside this custom plpgsql function, p1[1] is not valid syntax to
dereference the 1st element in p1.

Anyone knows how to solve this problem?

This is a good use of the 9.1 SLICE feature:

CREATE FUNCTION slice_hstore(text[]) RETURNS SETOF hstore AS $$
DECLARE
x text[];
BEGIN
FOREACH x SLICE 1 IN ARRAY $1
LOOP
return next hstore(x);
END LOOP;
END;
$$ LANGUAGE plpgsql;

select slice_hstore('{{g1, 1, f2, 123, f3, ABC}, {f1, 2, f2, 345, f3,
DEF}}'::text[]);

postgres=# select slice_hstore('{{g1, 1, f2, 123, f3, ABC}, {f1, 2,
f2, 345, f3, DEF}}'::text[]);
slice_hstore
-------------------------------------
"f2"=>"123", "f3"=>"ABC", "g1"=>"1"
"f1"=>"2", "f2"=>"345", "f3"=>"DEF"
(2 rows)

postgres=# select array(select slice_hstore('{{g1, 1, f2, 123, f3,
ABC}, {f1, 2, f2, 345, f3, DEF}}'::text[]));
?column?
-------------------------------------------------------------------------------------------------------
{"\"f2\"=>\"123\", \"f3\"=>\"ABC\", \"g1\"=>\"1\"","\"f1\"=>\"2\",
\"f2\"=>\"345\", \"f3\"=>\"DEF\""}
(1 row)

merlin