It Returns an array instead of a string

Question

can anyone help me with Returns an array instead of a string, as a beginner im having a hard time. thank you in advance

here is my code:

IF (sBnsStat= 'REN') THEN
        OPEN cPrevPermit  FOR SELECT  permit_no  FROM  buss_hist  WHERE bin = m_sBin AND tax_year < sTaxYear;

    DECLARE
                oPrevPermitRow pljson_list;
            BEGIN
                LOOP
                    FETCH cPrevPermit  INTO s_PrevPermitNo ;
                    EXIT WHEN cPrevPermit%NOTFOUND;
            oPrevPermitRow := pljson_list();
            oPrevPermitRow.append(s_PrevPermitNo);
            sPrevPermitNo.append(oPrevPermitRow.to_json_value);
                END LOOP;
            END;
            CLOSE cPrevPermit;
        END if;

             oBusinessInfo.put('prevPermitNo',sPrevPermitNo);

HERE is the result:

prevPermitNo":[[" "],[" "],[" "],["2019-00093"],["2020-00311"],["2021-00509"]

i need to convert this into string instead of array , the reason is that the site is crashing because of the array result

Does this answer your question? [Store select query's output in one array in postgres](https://stackoverflow.com/questions/6402043/store-select-querys-output-in-one-array-in-postgres) — Luuk, Aug 06 '23 at 07:11
To convert array to string, see: [How to use array_to_string in PostgreSQL to change column data type?](https://stackoverflow.com/questions/74708290/how-to-use-array-to-string-in-postgresql-to-change-column-data-type) — Luuk, Aug 06 '23 at 09:53

score 0 · Answer 1 · answered Aug 06 '23 at 07:38

This might do it for you:

IF (sBnsStat = 'REN') THEN
    OPEN cPrevPermit FOR SELECT permit_no FROM buss_hist WHERE bin = m_sBin AND tax_year < sTaxYear;
    
    DECLARE
        oPrevPermitRow pljson_list;
        oPrevPermits pljson_list := pljson_list();  -- Initialize an array for the permits
        
        -- Declare s_PrevPermitNo variable here if not already declared
        
    BEGIN
        LOOP
            FETCH cPrevPermit INTO s_PrevPermitNo;
            EXIT WHEN cPrevPermit%NOTFOUND;
            
            oPrevPermitRow := pljson_list();  
            
            oPrevPermitRow.append(s_PrevPermitNo);
            
            oPrevPermits.append(oPrevPermitRow);  
        END LOOP;
    END;

    CLOSE cPrevPermit;
    
    oBusinessInfo.put('prevPermitNo', oPrevPermits); 
END IF;

Initializze 'oPrevPermits' before loop, this will store the arrayy
Only append 'oPrevPermitRow ' to 'oPrevPermits' within loop

It Returns an array instead of a string

1 Answers1