PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Convert String array to integer array in Postgres

I have a string array that represents the days of the week, but I want to map it to an integer array.

I have created a function to do it, but it always returns null, I am sure I am missing something obvious but I cant see it.

create or replace function days_textArray_toIntArray(daysArray text[])
returns int[]
language plpgsql
set search_path = ''
as $$

declare

  textArray text[];
  intArray int[];

begin

  if ('Sun' = ANY(textArray)) then 
    intArray = array_append(intArray,0);
  elseif ('Mon' = ANY(textArray)) then 
    intArray = array_append(intArray,1);
  elseif ('Tue' = ANY(textArray)) then 
    intArray = array_append(intArray,2);
  elseif ('Wed' = ANY(textArray)) then 
    intArray = array_append(intArray,3);
  elseif ('Thu' = ANY(textArray)) then 
    intArray = array_append(intArray,4);
  elseif ('Fri' = ANY(textArray)) then 
    intArray = array_append(intArray,5);
  elseif ('Sat' = ANY(textArray)) then 
    intArray = array_append(intArray,6);
  end if;

  return intArray;

end;
$$

when I invoke it: select days_textArray_toIntArray(‘{Wed,Sun}’::text[]) I just get a null value returned when I would expect to get an array with [3,0] that corresponds to the text array entered.

Tags:

2

Answers


  1. 0

    Your function is returning null because you do not assign any value to the textArray variable, Therefore it remains as an empty array. Therefore, none of your conditions inside the if block will be true, and it will return an empty array.

    You might need to assign the input parameter daysArray to textArray in your function.

    Refer the below code for reference:

    CREATE OR REPLACE FUNCTION days_textArray_toIntArray(daysArray TEXT[])
RETURNS INT[]
LANGUAGE plpgsql
AS $$
DECLARE
  textArray TEXT[];
  intArray INT[] := '{}'; -- Initialize intArray as an empty array
BEGIN
  textArray := daysArray; -- Assign input parameter to textArray
  
  FOR i IN 1..array_length(textArray, 1) LOOP
    IF textArray[i] = 'Sun' THEN 
      intArray := intArray || 0;
    ELSIF textArray[i] = 'Mon' THEN 
      intArray := intArray || 1;
    ELSIF textArray[i] = 'Tue' THEN 
      intArray := intArray || 2;
    ELSIF textArray[i] = 'Wed' THEN 
      intArray := intArray || 3;
    ELSIF textArray[i] = 'Thu' THEN 
      intArray := intArray || 4;
    ELSIF textArray[i] = 'Fri' THEN 
      intArray := intArray || 5;
    ELSIF textArray[i] = 'Sat' THEN 
      intArray := intArray || 6;
    END IF;
  END LOOP;

  RETURN intArray;
END;
$$;
    Login or Signup to reply.
  2. 0

    You do not need a function for this; it can be done with a single SQL statement.

    select array_agg(ap::integer)
  from (select array_position(array['sun','mon','tue','wed','thu','fri','sat'],dl) - 1  ap
          from (select lower(substring(unnest(<Array of day names>),1,3)) dl)
       );

    How it works: It begins Unnesting the the array of names and standardizing to lower case and 3character length. It them extracts the position within the full array of day of week names (in desired order) and subtracting 1, since arrays in Postgres are 1-based indexes. Finally, it aggregates those results back into an array.
    Reference: Postgres array functions.

    If this is commonly used in your app the it can be defined as aSQL function. See Demo here:

    create or replace function days_textArray_toIntArray(daysArray text[])
  returns int[]
  language sql
as $$
    select array_agg(ap::integer)
      from (select array_position(array['sun','mon','tue','wed','thu','fri','sat'],dl) - 1  ap
              from (select lower(substring(unnest(daysArray),1,3)) dl)
           );
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search