Re: Associative array in Pl/PgSQL

On Sat, May 4, 2013 at 6:40 PM, Karel Riveron Escobar
<kescobar(at)estudiantes(dot)uci(dot)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