Thread: Function with Integer array parameter

Postgres Gurus,
                             Please suggest me what is wrong with this
function.
This function tries to retrieve set of rows from description table based
on set of ID fields passed as array.

The error, I get is : ERROR:  only one AS item needed for language "plpgsql"

CREATE OR REPLACE function get_description_list(integer[]) RETURNS SETOF
type_description AS
'DECLARE
    ints_desc_ids ALIAS FOR $1;
        desc_rec __i18n.type_description%ROWTYPE;
BEGIN

    FOR desc_rec IN
        SELECT  d_base.id AS description_id,
           ''en'' AS iso_lang,
            ''US'' AS iso_country_a2,
            0 as description_type_id,
            d_base.description_text AS description_text
        FROM     description AS d_base
        WHERE   d_base.id in array_to_string(ints_desc_ids alias,',')
    LOOP
            RETURN NEXT desc_rec;
        END LOOP;
        RETURN;
  END;'
LANGUAGE plpgsql;

On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote:
> Postgres Gurus,
>                             Please suggest me what is wrong with this
> function.
> This function tries to retrieve set of rows from description table based
> on set of ID fields passed as array.
>
> The error, I get is : ERROR:  only one AS item needed for language "plpgsql"
>
> CREATE OR REPLACE function get_description_list(integer[]) RETURNS SETOF
> type_description AS
> 'DECLARE
...
>        WHERE   d_base.id in array_to_string(ints_desc_ids alias,',')

Note the quotes.

Use dollar quoting... it$$s your friend.
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Decibel! wrote:

On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote: 

Postgres Gurus,                           Please suggest me what is wrong with this 
function.
This function tries to retrieve set of rows from description table based 
on set of ID fields passed as array.

The error, I get is : ERROR:  only one AS item needed for language "plpgsql"

CREATE OR REPLACE function get_description_list(integer[]) RETURNS SETOF 
type_description AS
'DECLARE   

... 

       WHERE   d_base.id in array_to_string(ints_desc_ids alias,',')   

Note the quotes.

Use dollar quoting... it$$s your friend. 

I tries wir $$ as well as ''(two single quotes instead of one single quote) but still got the same error.
I think the error is with

  WHERE   d_base.id in array_to_string(ints_desc_ids alias,',').
I need a way using integer array in where clause. 

On Aug 16, 11:06 am, rbai...@nyc.yamaha.com (Ranjan Kumar Baisak)
wrote:
> Decibel! wrote:
> > On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote:
>
> >> Postgres Gurus,
> >>                             Please suggest me what is wrong with this
> >> function.
> >> This function tries to retrieve set of rows from description table based
> >> on set of ID fields passed as array.
>
> >> The error, I get is : ERROR:  only one AS item needed for language "plpgsql"
>
> >> CREATE OR REPLACE function get_description_list(integer[]) RETURNS SETOF
> >> type_description AS
> >> 'DECLARE
>
> > ...
>
> >>        WHERE   d_base.id in array_to_string(ints_desc_ids alias,',')
>
> > Note the quotes.
>
> > Use dollar quoting... it$$s your friend.
>
> I tries wir $$ as well as ''(two single quotes instead of one single
> quote) but still got the same error.
> I think the error is with
>
>   WHERE   d_base.id in array_to_string(ints_desc_ids alias,',').
> I need a way using integer array in where clause.

... WHERE d_base.id = ANY(ints_desc_ids) ...

See:
http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5865