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 — Luuk
– Luuk, Commented Aug 6, 2023 at 7:11
To convert array to string, see: How to use array_to_string in PostgreSQL to change column data type? — Luuk
– Luuk, Commented Aug 6, 2023 at 9:53

Nisar Mehmood · Accepted Answer · 2023-08-06 07:38:24Z

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

Collectives™ on Stack Overflow

It Returns an array instead of a string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related