Dynamic Array into pl/pgSQL function

#1

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 varcharis not the correct input type for the ListOfNumbers, but am unsure what touse 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

#2

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 (AT) grifflink (DOT) com>
To: <pgsql-novice (AT) postgresql (DOT) org>
Sent: Sun, 30 May 2004 23:31:17 -0600
Subject: [NOVICE] Dynamic Array into pl/pgSQL function

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#3

On Mon, 2004-05-31 at 07:24, derrick wrote:

Quote:

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"

....

Quote:

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.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 broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#4

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

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#5

On Mon, 2004-05-31 at 16:04, derrick wrote:

Quote:

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.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 broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#6

On Mon, 2004-05-31 at 18:31, Oliver Elphick wrote:

Quote:

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 (AT) lfix (DOT) 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 broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#7

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 (AT) lfix (DOT) co.uk>
To: derrick <derrick (AT) grifflink (DOT) com>
Cc: pgsql-novice (AT) postgresql (DOT) org
Sent: Mon, 31 May 2004 18:51:34 +0100
Subject: Re: [NOVICE] Dynamic Array into pl/pgSQL function

Quote:

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 (AT) lfix (DOT) 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 -------

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#8

derrick wrote:

Quote:

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

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#9

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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#10

David Cruz <davidcrmail (AT) yahoo (DOT) com> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org