Skip site navigation (1) Skip section navigation (2)

Re: Dynamic Array into pl/pgSQL function

From: "derrick" <derrick(at)grifflink(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Dynamic Array into pl/pgSQL function
Date: 2004-05-31 15:04:50
Message-ID: 20040531145746.M55436@grifflink.com (view raw or flat)
Thread:
Lists: pgsql-novice
Okay...

I have tried this: AND caseId = ANY (ListOfNumbers)
   and get this error "parser: parse error at or near "$2" at character 1060"
I have tried this: AND caseId = ANY (ListOfNumbers[])
   and get this error "parser: parse error at or near "$2" at character 1060"
I have tried this: AND caseId = ANY (SELECT ListOfNumbers[])
   and get this error "parser: parse error at or near "]" at character 1071"

The function only works when I assign an array element such as "ListOfNumbers
[1]".  In this case it works, however now I have lost the intent of the 
function using all the numbers in the array.  This method only uses one.

Is there a way to extract all of the elements from the array, no matter the 
size of the array?

Thanks,
Derrick

--


---------- Original Message -----------
From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: derrick <derrick(at)grifflink(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Sent: Mon, 31 May 2004 08:03:54 +0100
Subject: Re: [NOVICE] Dynamic Array into pl/pgSQL function

> On Mon, 2004-05-31 at 07:24, derrick wrote:
> > Okay.  I can send the numbers to the function using this array 
> > format:  '{123,124,125,126}'
> > And the function receives those numbers in this format:  CREATE OR 
REPLACE 
> > FUNCTION public.PopContacts(varchar, int4[]).
> > 
> > But, I still can't use the $2 or the ListOfNumbers alias in the function 
> > body.  I get this error:  "Unable to identify an operator '=' for 
> > types 'integer' and 'integer[]'  You will have to retype this query using 
an 
> > explicit cast"
> 
> ...
> 
> > >   FOR rec IN SELECT caseid, name, address FROM Table1 WHERE area = 
> > > State and caseId In (ListOfNumbers)  LOOP
> 
> I've never needed to use it, but I think you need the operator "ANY"
> rather then "IN" - see 7.4 docs section 9.17.3
> 
> -- 
> Oliver Elphick                                          olly(at)lfix(dot)co(dot)uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
>                  ========================================
>      "How precious also are thy thoughts unto me, O God! how
>       great is the sum of them! If I should count them, they
>       are more in number than the sand; when I awake, I am 
>       still with thee."    Psalms 139: 17,18
------- End of Original Message -------


In response to

Responses

pgsql-novice by date

Next:From: Oliver ElphickDate: 2004-05-31 17:31:53
Subject: Re: Dynamic Array into pl/pgSQL function
Previous:From: Rajan BhideDate: 2004-05-31 12:30:30
Subject: Re: Error msgs from PostgreSQL server : specified item offset is too large, failed to add item to the page

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group