Обсуждение: Dynamic Array into pl/pgSQL function

 I looked around for an example of how I might accomplish this, but couldn't find anything.  Perhaps I'm using the wrong search words.

I want to input dynamic values into a function, with one of those values being a list of numbers:

CREATE OR REPLACE FUNCTION public.PopContacts(varchar, varchar)
  RETURNS SETOF casedata AS
'
DECLARE
c casedata%rowtype;
State alias for $1;
ListOfNumbers alias for $2;
rec RECORD;

BEGIN
  FOR rec IN SELECT caseid, name, address FROM Table1 WHERE area = State and caseId In (ListOfNumbers)
  LOOP
    c.caseid := rec.caseid;  c.name := rec.name; c.address := rec.name;
  RETURN NEXT c;
  END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

How can I get the ListOfNumbers into the function and then have the function use that ListOfNumbers in the manner shown above?  I realize that varchar is not the correct input type for the ListOfNumbers, but am unsure what to use to have it work properly. The length of the ListOfNumbers varies with each call to the function.  I am sending a Query string to the server from a client application.

I appreciate any ideas anyone may have.

Thank you,
Derrick

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"

Any thoughts?

--


---------- Original Message -----------
From: "Derrick Betts" <derrick@grifflink.com>
To: <pgsql-novice@postgresql.org>
Sent: Sun, 30 May 2004 23:31:17 -0600
Subject: [NOVICE] Dynamic Array into pl/pgSQL function

> I looked around for an example of how I might accomplish this, but
> couldn't find anything.  Perhaps I'm using the wrong search words.
>
> I want to input dynamic values into a function, with one of those
> values being a list of numbers:
>
> CREATE OR REPLACE FUNCTION public.PopContacts(varchar, varchar)
>   RETURNS SETOF casedata AS
> '
> DECLARE
> c casedata%rowtype;
> State alias for $1;
> ListOfNumbers alias for $2;
> rec RECORD;
>
> BEGIN
>   FOR rec IN SELECT caseid, name, address FROM Table1 WHERE area =
> State and caseId In (ListOfNumbers)  LOOP
>     c.caseid := rec.caseid;  c.name := rec.name; c.address := rec.name;
>   RETURN NEXT c;
>   END LOOP;
> RETURN;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
>
> How can I get the ListOfNumbers into the function and then have the
> function use that ListOfNumbers in the manner shown above?  I
> realize that varchar is not the correct input type for the
> ListOfNumbers, but am unsure what to use to have it work properly.
> The length of the ListOfNumbers varies with each call to the
> function.  I am sending a Query string to the server from a client
application.
>
> I appreciate any ideas anyone may have.
>
> Thank you,
> Derrick
------- End of Original Message -------

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

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@lfix.co.uk>
To: derrick <derrick@grifflink.com>
Cc: pgsql-novice@postgresql.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@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 -------

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

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

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

derrick wrote:
> 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"

[...snip example...]
> Any other ideas? I'm using 7.4.2

I'd bet not. Show us the result of:

select version();

Your example works perfectly for me under 7.4.2, and gives the same
error you see under 7.3.6.

HTH,

Joe

Hello to everyone!

I'm trying to make a new data type in this way: (like
the docs says)

pruebas1=> create type newtype as (number integer, str
char(20));
ERROR:  parser: parse error at or near "as"


And I get that error, could somebody help me?...I'm
using  PostgreSQL 7.4

thanks..

David Cruz




__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

David Cruz <davidcrmail@yahoo.com> writes:
> pruebas1=> create type newtype as (number integer, str
> char(20));
> ERROR:  parser: parse error at or near "as"

> And I get that error, could somebody help me?...I'm
> using  PostgreSQL 7.4

I'd bet not.  That example works fine in 7.4 for me.  The
syntax error is exactly what I get in 7.2 though...

            regards, tom lane

You are absolutely right!  I installed 7.4.2 on the server but for some
reason (My lack of experience) I was still using 7.3.2 thinking I was
using the new version.  The problem has been rectified, and we are now
on 7.4.2 and the array feature works perfectly.  Thanks to everyone for
your help.

Derrick

Joe Conway wrote:

> derrick wrote:
>
>> 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"
>
>
> [...snip example...]
>
>> Any other ideas? I'm using 7.4.2
>
>
> I'd bet not. Show us the result of:
>
> select version();
>
> Your example works perfectly for me under 7.4.2, and gives the same
> error you see under 7.3.6.
>
> HTH,
>
> Joe
>
>
>
>