How to call the substring() function with variables for the start & count parameters? Below example code is not working.

DO
$$
 declare
    v_idx numeric(3);
    v_ch char(1);
    v_string varchar(50);
 begin
     v_idx := 1;
     v_string := 'My String';
      WHILE v_idx < LENGTH(v_string)
     LOOP
        v_ch := substring(v_string, v_idx, 1);  -- here!
        raise notice 'Value: %', v_ch;
        v_idx := v_idx   1;
     END LOOP;
end;
$$;

CodePudding user response：

This works:

DO
$$
DECLARE
   _string text := 'My String';
   _start  int := 1;            -- integer!
   _count  int := 1;            -- integer!
   _substr text;
BEGIN
   FOR _start IN 1 .. length(_string)
   LOOP
      _substr := substring(_string, _start, _count);
      RAISE NOTICE 'Substring from % for %: %', _start, _count, _substr;
   END LOOP;
END
$$;

Produces:

NOTICE:  Substring from 1 for 1: M
NOTICE:  Substring from 2 for 1: y
NOTICE:  Substring from 3 for 1:  
NOTICE:  Substring from 4 for 1: S
NOTICE:  Substring from 5 for 1: t
NOTICE:  Substring from 6 for 1: r
NOTICE:  Substring from 7 for 1: i
NOTICE:  Substring from 8 for 1: n
NOTICE:  Substring from 9 for 1: g

Mostly because substring() expects integer input for start and count (not numeric).
There was also an off-by-1 error in your loop, which I replaced with a simpler, cheaper, correct FOR loop.

You can init variables at declaration time.

BTW, the documented standard SQL syntax for substring() is:

substring(_string FROM _start FOR _count)

But the Postgres implementation with just commas works, too.