how to assign variable to unnest in PostgreSQL-CodePudding

I have tried assign variable(W_SPLIT_MSG) but getting error like ERROR: syntax error at or near "SELECT" LINE 55: W_SPLIT_MSG:= SELECT UNNEST(fn_split('P_MAIL_ID',';')); please help me to solve this error

W_SPLIT_MSG:= SELECT UNNEST(fn_split('P_MAIL_ID',';'));   RAISE NOTICE 'GOT HERE :% IS THE NEW VALUE',W_SPLIT_MSG;
          FOR I IN 1 .. W_SPLIT_MSG.COUNT LOOP
        INSERT INTO EMAILSAFRCP
          (ENTITY_CODE,
           EMAIL_SEQ_NO,
           EMAIL_RCP_TYPE,
           EMAIL_DTL_SL,    
           EMAIL_ID)
        VALUES
          (P_ENTITY_NUM, P_SEQ_NUM, 'T', I, W_SPLIT_MSG(I));
      END LOOP;




CREATE OR REPLACE FUNCTION public.fn_split(
    inputstr text,
    delimeter text)
    RETURNS text[]
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE 
 elems text[];
BEGIN 
 SELECT string_to_array(inputstr,delimeter) INTO elems;
 RETURN elems;
END; 
$BODY$;

CodePudding user response：

Your code is way too complicated for such a simple task. But you don't need unnest to iterate over an array. To do that use FOREACH

However as you seem to also need the array index, you indeed need a regular FOR loop. But an array has no .count property. You need to use the cardinality() function to get the size of the array:

Note that 'P_MAIL_ID' is a string value, so fn_split('P_MAIL_ID',';') won't "split" anything as the string 'P_MAIL_ID' doesn't contain the delimiter. You probably meant to write fn_split(P_MAIL_ID,';')

declare
  -- note W_SPLIT_MSG needs to be an array!
  W_SPLIT_MSG text[];
begin
 ....
 -- no SELECT required for an assignment
 -- no unnest required because you want to use the array to iterate over it
 W_SPLIT_MSG := fn_split(P_MAIL_ID,';');

 FOR i IN IN 1..cardinality(W_SPLIT_MSG) LOOP
    INSERT INTO EMAILSAFRCP
      (ENTITY_CODE,
       EMAIL_SEQ_NO,
       EMAIL_RCP_TYPE,
       EMAIL_DTL_SL,    
       EMAIL_ID)
    VALUES
      (P_ENTITY_NUM, P_SEQ_NUM, 'T', i, W_SPLIT_MSG[i]);
  END LOOP;

But this is way too complicated. There is no LOOP required, a simple insert ... select will do just fine:

    INSERT INTO EMAILSAFRCP
      (ENTITY_CODE,
       EMAIL_SEQ_NO,
       EMAIL_RCP_TYPE,
       EMAIL_DTL_SL,    
       EMAIL_ID)
    select P_ENTITY_NUM, P_SEQ_NUM, 'T', x.i, x.item
    from unnest(fn_split(P_MAIL_ID,';')) with ordinality as x(item, i);