Thread: Associative array in Pl/PgSQL

And, I need work with it into a Pl/PgSQL function. How can I do this?

SQL has no such concept as 'associative arrays'.  it understands relations (tables).

maybe convert it to hstore format, and pass it in as a text string, but your example gets even messier in that you've nested these associative things.

would be much more appropriate to store that data as tables in postgresql.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

And, I need work with it into a Pl/PgSQL function. How can I do this?

SQL has no such concept as 'associative arrays'.  it understands relations (tables).

maybe convert it to hstore format, and pass it in as a text string, but your example gets even messier in that you've nested these associative things.

would be much more appropriate to store that data as tables in postgresql.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 2013-05-04, Karel Riveron Escobar <kescobar@estudiantes.uci.cu> wrote:
> --=_be60f7f0-365e-4e0a-98b5-f8b13a8ea728
> Content-Type: text/plain; charset=utf-8
> Content-Transfer-Encoding: quoted-printable
>
> Hello everyone,
>
>
> I have a problem with Pl/PgSQL function. I need to pass it as parameter an =
> associative array. I have no idea how to do that. C an s omebody help me?
>
>
> To be more specific, I have an associative array in PHP. Something like thi=
> s:

 [...]

> And, I need work with it into a Pl/PgSQL function. How can I do this?

possibly this can be done using hstore and/or JSON



--
⚂⚃ 100% natural

On 2013.05.04 4:40 PM, Karel Riveron Escobar wrote:
> Hello everyone,
>
> I have a problem with Pl/PgSQL function. I need to pass it as parameter an
> associative array. I have no idea how to do that. Can somebody help me?
>
> To be more specific, I have an associative array in PHP. Something like this:
>
> array(
>       'name' => 'Robert',
>       'age' => 24,
>       'teachers' => array(
>                                     array('name'=>'Sean'),
>                                     array('name'=>'Megan')
>                              )
> );
>
> And, I need work with it into a Pl/PgSQL function. How can I do this?

The answer to your question depends on a number of factors about the structure
of your associative array.

If the structure is heterogeneous, where each element might be a different data
type, and if the keys of your array are all text analogous to attribute names,
then a SQL tuple/row is the most direct analogy, so use some ROW type with one
attribute per associative array element.

Conversely, if the structure is homogeneous, where all elements have the same
key types and same value types, then a binary/2-attribute/column relation/table
is the most direct analogy, so then use a TABLE(key text,value whatever) type.

 From your example though, it looks like a ROW is definitely what you want.

-- Darren Duncan

On Sat, May 4, 2013 at 6:40 PM, Karel Riveron Escobar
<kescobar@estudiantes.uci.cu> wrote:
>
> Hello everyone,
>
> I have a problem with Pl/PgSQL function. I need to pass it as parameter an
> associative array. I have no idea how to do that. Can somebody help me?
>
> To be more specific, I have an associative array in PHP. Something like
> this:
>
> array(
>      'name' => 'Robert',
>      'age' => 24,
>      'teachers' => array(
>                                    array('name'=>'Sean'),
>                                    array('name'=>'Megan')
>                             )
> );
>
> And, I need work with it into a Pl/PgSQL function. How can I do this?

As long as the array is immutable (and even then it works with some
stipulations) in the function, you can do it:

create type teacher_t as (name text);
create type student_t as (name text, age int, teachers teacher_t[]);

select
  row(
    'Robert',
    24,
    array
    [
      row('Sean'),
      row('Megan')
    ]::teacher_t[]
  )::student_t;

              row
--------------------------------
 (Robert,24,"{(Sean),(Megan)}")


This is how we return the data in fancy was as json:

select
  row_to_json(row(
    'Robert',
    24,
    array
    [
      row('Sean'),
      row('Megan')
    ]::teacher_t[]
  )::student_t);

                               row_to_json
--------------------------------------------------------------------------
 {"name":"Robert","age":24,"teachers":[{"name":"Sean"},{"name":"Megan"}]}


The problem is that until 9.3 comes out there will be no convenient
way to pass complicated structures from php as it doesn't understand
postgres types and arrays without complicated text hacking.   Post
9.3, you just use php json to pass data.  Of course, you can always
stage data to a table (this works now), but like you I find it to be
pretty lame when all you're trying to do is execute a function.

merlin