Re: Dynamic Array into pl/pgSQL function

There must be some kind of setting that needs to be set on my database that
isn't, because I have copied what you used nearly exactly, and I still get
this message: "WARNING:  line 4 at for over select rows  ERROR:  parser:
parse error at or near "$1" at character 55"

The function I'm using is:
CREATE OR REPLACE FUNCTION public.practice(integer[])
  RETURNS SETOF integer LANGUAGE 'plpgsql' AS '
DECLARE
  i RECORD;
BEGIN
  FOR i IN SELECT groupid FROM ClientInfo WHERE groupid = ANY ($1) LOOP
    RETURN NEXT i.groupid;
  END LOOP;
  RETURN;
END;';

The statement I run against it is:
Select * from practice('{96,101,110,112}');

Any other ideas? I'm using 7.4.2

Thanks again,
Derrick

--


---------- Original Message -----------
From: Oliver Elphick <olly@lfix.co.uk>
To: derrick <derrick@grifflink.com>
Cc: pgsql-novice@postgresql.org
Sent: Mon, 31 May 2004 18:51:34 +0100
Subject: Re: [NOVICE] Dynamic Array into pl/pgSQL function

> On Mon, 2004-05-31 at 18:31, Oliver Elphick wrote:
> > 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.
>
> Sorry, I saw that you had changed this.
>
> Here is a simple example that works as a function:
>
> junk=# CREATE OR REPLACE FUNCTION x(INTEGER[])
>   RETURNS SETOF INTEGER LANGUAGE 'plpgsql' AS '
> DECLARE
>   i   RECORD;
> BEGIN
>   FOR i IN SELECT f1 FROM ci WHERE f1 = ANY ($1) LOOP
>     RETURN NEXT i.f1;
>   END LOOP;
>   RETURN;
> END;';
>
> junk=# select * from x('{1,2,3,4,5,6,7,8}');
>  x
> ---
>  2
>  6
>  1
>  8
> (4 rows)
>
> --
> Oliver Elphick                                          olly@lfix.co.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 -------