Re: How to dereference 2 dimensional array?

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