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

Re: Dynamic Array into pl/pgSQL function

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: derrick <derrick(at)grifflink(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Dynamic Array into pl/pgSQL function
Date: 2004-05-31 17:31:53
Message-ID: 1086024713.27765.33.camel@linda (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, 2004-05-31 at 16:04, derrick wrote:
> 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?

It does work:

        junk=# select * from ci order by f1;
         f1
        -----
           5
          34
          45
          67
          69
         123
        (6 rows)
         
        junk=# select * from ci where f1 = any ('{34,123,5}') order by
        f1;
         f1
        -----
           5
          34
         123
        (3 rows)

In your original function definition, you declared the function as
taking (varchar, varchar).  I think that should be (varchar,
varchar[]).  If you haven't changed that, you are passing an array of
varchars into a parameter that expects a scalar varchar.

-- 
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 


In response to

Responses

pgsql-novice by date

Next:From: Oliver ElphickDate: 2004-05-31 17:51:34
Subject: Re: Dynamic Array into pl/pgSQL function
Previous:From: derrickDate: 2004-05-31 15:04:50
Subject: Re: Dynamic Array into pl/pgSQL function

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