Check for integer in string array

Question

I am trying to check a string array for existence of a converted integer number. This sits inside of a procedure where:

nc_ecosite is an integer variable
current_consite is a string array
ecosite is an integer
current_ecosite_nc is double

IF to_char(nc_ecosite, '999') IN
   (select current_consite from current_site_record
    where current_ecosite_nc::integer = nc_ecosite) THEN
       ecosite := nc_ecosite;

The result always comes from the ELSIF that follows the first IF. This occurs when nc_ecosite is in the array (from checks). Why is ecosite not being populated with nc_ecosite when values are matching?

I am working with Postgres 9.3 inside pgAdmin.

Any chance you can post more of the function, along with inputs, expected outputs and actual outputs? I sort of follow you, but some concrete examples would help to see what you want vs what is happening. — Hambone, Dec 15 '15 at 02:59

score 0 · Answer 1 · edited May 23 '17 at 12:02

The immediate reason for the problem is that to_char() inserts a leading blank for your given pattern (legacy reasons - to make space for a potential negative sign). Use the FM Template Pattern Modifier to avoid that:

to_char(nc_ecosite, 'FM999')

Of course, it would be best to operate with matching data types to begin with - if at all possible.

Barring that, I suggest this faster and cleaner statement:

   SELECT INTO ecosite  nc_ecosite  -- variable or column??
   WHERE  EXISTS (
      SELECT 1 FROM current_site_record c
      WHERE  current_ecosite_nc::integer = nc_ecosite
      AND    to_char(nc_ecosite, 'FM999') = ANY(current_consite)
      );

   IF NOT FOUND THEN  ... -- to replace your ELSIF

Make sure you don't run into naming conflicts between parameters, variables and column names! A widespread convention is to prepend variable names with _ (and never use the same for column names). But you better table-qualify column names in all queries anyway. You did not make clear which is a column and which is a variable ...

I might be able to optimize the statement further if I had the complete function and table definition.

score 0 · Accepted Answer · answered Dec 15 '15 at 17:30

I found the following to provide the desired result:

IF nc_ecosite in 
(select (unnest(string_to_array(current_consite, ',')))::integer 
from current_site_record 
where current_ecosite_nc::integer = nc_ecosite) THEN
   ecosite := nc_ecosite::integer;

Check for integer in string array

2 Answers2

Linked