By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

help using arrays in a function

P: n/a
Hello,

I have a rather simple function I've been using in 7.3.4

version
------------------------------------------------------------------------
-----------------
PostgreSQL 7.3.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2
20020927 (prerelease)
(1 row)

This compares an integer to all elements in an integer array and looks
for a match

CREATE OR REPLACE FUNCTION public.array_element_compare(_int4, int4)
RETURNS text AS
'
DECLARE
a ALIAS FOR $1;
b ALIAS FOR $2;
i integer;
status text;
BEGIN
i := 1;
status := ''false'';
WHILE a[i] LOOP
IF a[i] = b THEN
status := ''true'';
END IF;
i := i+1;
END LOOP;
RETURN status;
END;'
LANGUAGE 'plpgsql';

It works find

germinate1.5 =# select array_element_compare('{100,92,82}', 82);
array_element_compare
-----------------------
true
(1 row)

germinate1.5 =# select array_element_compare('{100,92}', 82);
array_element_compare
-----------------------
false
(1 row)

I'm now testing our database on 7.4 and get an error with this function
and I can't figure out how to fix it.

version
------------------------------------------------------------------------
-------------
PostgreSQL 7.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.3.1
(cygming special)
(1 row)

germinate1.5 =# select array_element_compare('{100, 92, 82}', 82);
ERROR: invalid input syntax for type boolean: "100"
CONTEXT: PL/pgSQL function "array_element_compare" line 9 at while

Can anyone point me in the right direction?

Thanks much for any help,
Jennifer



************************************************** ***************
DISCLAIMER:

This email is from the Scottish Crop Research Institute, but the views expressed by the sender are not necessarily the views of SCRI and its subsidiaries. This email and any files transmitted with it are confidential to the intended recipient at the e-mail address to which it has been addressed. It may not be disclosed or used by any other than that addressee.
If you are not the intended recipient you are requested to preserve this confidentiality and you must not use, disclose, copy, print or rely on this e-mail in any way. Please notify ma**@scri.sari.ac.uk quoting the name of the sender and delete the email from your system.

Although SCRI has taken reasonable precautions to ensure no viruses are present in this email, neither the Institute nor the sender accepts any responsibility for any viruses, and it is your responsibility to scan the email and the attachments (if any).

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
"Jennifer Lee" <jl**@scri.sari.ac.uk> writes:
WHILE a[i] LOOP I'm now testing our database on 7.4 and get an error with this function
and I can't figure out how to fix it.
ERROR: invalid input syntax for type boolean: "100"
CONTEXT: PL/pgSQL function "array_element_compare" line 9 at while


plpgsql now enforces that the test expression of IF, WHILE, etc must be
a boolean. The above is not.

This is pretty poor coding practice anyway, since even before 7.4 it
would have given wrong answers for arrays containing zeroes or arrays
whose lower index bound is not 1. I'd suggest using the array_lower and
array_upper functions to determine the valid range of subscripts.
Something like

FOR i IN array_lower(a,1) .. array_upper(a,1) LOOP

should work.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #2

P: n/a
Jennifer Lee wrote:
WHILE a[i] LOOP


This problem has nothing to do with arrays, per se. PL/pgSQL no longer
assumes it can coerce an integer constant to a boolean. Instead make the
above line look like this:

WHILE a[i] IS NOT NULL LOOP
BTW, in 7.4 you could more easily get the same result like this:

regression=# select 82 = any ('{100,92,82}');
?column?
----------
t
(1 row)

regression=# select 82 = any ('{100,92}');
?column?
----------
f
(1 row)

See:
http://www.postgresql.org/docs/curre...mparisons.html

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.