Thread: Help with a plperl function

Hi,
i am having a problem with a plperl function. bellow function always
returns me an error saying "elements of Perl result array must be
reference to hash"

Can anyone point me out what i am doing wrong here?
Basically i try to return data from a select in a specific record format.
(i know that bellow example does not require a function, just want to
get a feel for it.)

Thanks in advance for any tips.

Alex


DROP FUNCTION get_item ();
DROP TYPE my_items;

CREATE TYPE my_items AS (
  item_id             INTEGER,
  description         TEXT,
  order_date          DATE);

CREATE FUNCTION get_item () RETURNS SETOF my_items AS $$

  my $res = [];
  my $rv = spi_exec_query('SELECT * FROM items LIMIT 10');

  my $nrows = $rv->{processed};
    foreach my $rn (0 .. $nrows - 1) {

        my $row = $rv->{rows}[$rn];
        my @record;

        $record[0] = $row->{item_id};
        $record[1] = $row->{item_descr};
        $record[2] = $row->{start_date};

        push @$res, @record;
    }
    return $res;


$$ LANGUAGE plperl;

On Tue, Apr 19, 2005 at 03:00:07AM +1000, Alex wrote:
>
> i am having a problem with a plperl function. bellow function always
> returns me an error saying "elements of Perl result array must be
> reference to hash"
[snip]
>        my $row = $rv->{rows}[$rn];
>        my @record;
>
>        $record[0] = $row->{item_id};
>        $record[1] = $row->{item_descr};
>        $record[2] = $row->{start_date};
>
>        push @$res, @record;

You should be pushing a hash reference, not an array.  Try something
like this:

    my $row = $rv->{rows}[$rn];

    push @$res, {item_id     => $row->{item_id},
                 description => $row->{item_descr},
                 order_date  => $row->{start_date}};

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In article <4263E797.8000500@meerkatsoft.com>,
Alex <alex@meerkatsoft.com> writes:

> Hi,
> i am having a problem with a plperl function. bellow function always
> returns me an error saying "elements of Perl result array must be
> reference to hash"

> Can anyone point me out what i am doing wrong here?
> Basically i try to return data from a select in a specific record format.
> (i know that bellow example does not require a function, just want to
> get a feel for it.)

> Thanks in advance for any tips.

> Alex


> DROP FUNCTION get_item ();
> DROP TYPE my_items;

> CREATE TYPE my_items AS (
>   item_id             INTEGER,     description         TEXT,
> order_date          DATE);

> CREATE FUNCTION get_item () RETURNS SETOF my_items AS $$
>   my $res = [];
>   my $rv = spi_exec_query('SELECT * FROM items LIMIT 10');
>   my $nrows = $rv->{processed};
>     foreach my $rn (0 .. $nrows - 1) {
>         my $row = $rv->{rows}[$rn];
>         my @record;
>         $record[0] = $row->{item_id};
>         $record[1] = $row->{item_descr};
>         $record[2] = $row->{start_date};
>         push @$res, @record;

This is the culprit.  You should instead use something like

push @$res, {
  item_id     => $row->{item_id},
  description => $row->{item_descr},
  order_date  => $row->{start_date},
};