Hello there,

When assigning mobilenumbers to a varchar[] in a loop through results it gives me the following error:

"array subscript in assignment must not be null"

Also, i think the query that joins member uuids, and group member uuids, into one, grouped on the user_id, i think it can be done better, or maybe this is even why it is going wrong in the first place!

Any help is very appreciated..

Thank you very much!

CREATE  OR REPLACE FUNCTION create_membermessage(in_company_uuid uuid, in_user_uuid uuid, in_destinationmemberuuids uuid[], in_destinationgroupuuids uuid[], in_title character varying, in_messagecontents character varying, in_timedelta interval, in_messagecosts numeric, OUT out_status integer, OUT out_status_description character varying, OUT out_value VARCHAR[], OUT out_trigger uuid[]) RETURNS record
    LANGUAGE plpgsql
    AS $$

DECLARE
    temp_count INTEGER;
    temp_costs NUMERIC;
    temp_balance NUMERIC;
    temp_campaign_uuid UUID;
    temp_record RECORD;
    temp_mobilenumbers VARCHAR[];
    temp_destination_uuids UUID[];
    temp_iterator INTEGER;
BEGIN

 out_status := NULL;
 out_status_description := NULL;
 out_value := NULL;
 out_trigger := NULL;



    SELECT INTO temp_count COUNT(*) FROM costs WHERE costtype = 'MEMBERMESSAGE' AND company_uuid = in_company_uuid AND startdatetime < NOW() AND (enddatetime > NOW() OR enddatetime IS NULL);

    IF temp_count > 1 THEN

        out_status := 1;
        out_status_description := 'Invalid rows in costs table!';
        RETURN;

    ELSEIF temp_count = 1 THEN

        SELECT INTO temp_costs costs FROM costs WHERE costtype = 'MEMBERMESSAGE' AND company_uuid = in_company_uuid AND startdatetime < NOW() AND (enddatetime > NOW() OR enddatetime IS NULL);

    ELSE

        SELECT INTO temp_costs costs FROM costs WHERE costtype = 'MEMBERMESSAGE' AND company_uuid IS NULL AND startdatetime < NOW() AND (enddatetime > NOW() OR enddatetime IS NULL);

    END IF;

    IF temp_costs != in_messagecosts THEN

        out_status := 2;
        out_status_description := 'Message costs have changed during sending of the message';
        RETURN;

    ELSE

        SELECT INTO temp_balance balance FROM companies WHERE company_uuid = in_company_uuid;
        SELECT INTO temp_count COUNT(*) 
          FROM users 
          WHERE (user_uuid = ANY(in_destinationmemberuuids))
             OR (user_uuid IN (SELECT user_uuid FROM targetgroupusers WHERE targetgroup_uuid = ANY(in_destinationgroupuuids)) )
         GROUP BY user_uuid;

        temp_campaign_uuid := generate_uuid('campaigns', 'campaign_uuid');
        INSERT INTO campaigns (company_uuid, campaign_uuid, title, senddatetime, startdatetime, enddatetime, messagetype, state, message) VALUES (in_company_uuid, temp_campaign_uuid, in_title, NOW() + in_timedelta, NOW() + in_timedelta, NOW() + in_timedelta, 'MEMBERMESSAGE', 'DRAFT', in_messagecontents);

        IF in_timedelta > '00:00:00' THEN


        ELSE

            IF temp_balance < (temp_costs * temp_count) THEN

                UPDATE campaigns SET state = 'INACTIVE' WHERE campaign_uuid = temp_campaign_uuid;
                out_status := 2;
                out_status_description := 'Insufficient balance';
                RETURN;

            ELSE

                UPDATE campaigns SET state = 'ACTIVE' WHERE campaign_uuid = temp_campaign_uuid;

                UPDATE companies SET balance = (temp_balance - (temp_costs * temp_count)) WHERE company_uuid = in_company_uuid;

                SELECT INTO temp_destination_uuids array_agg(DISTINCT(user_uuid)) 
                  FROM users 
                  WHERE (user_uuid = ANY(in_destinationmemberuuids))
                     OR (user_uuid IN(SELECT user_uuid FROM targetgroupusers WHERE targetgroup_uuid = ANY(in_destinationgroupuuids)));
RAISE NOTICE 'Array is %', temp_destination_uuids;  


                FOR temp_record IN (SELECT u.firstname, m.mobilenumber FROM users AS u LEFT JOIN mobilenumbers AS m ON m.user_uuid = u.user_uuid WHERE u.user_uuid = ANY(temp_destination_uuids)) LOOP
                    IF temp_record.mobilenumber IS NOT NULL AND temp_record.mobilenumber != '' THEN

--THIS IS WHERE IT GOES WRONG
temp_mobilenumbers[temp_iterator] := ARRAY[temp_record.firstname::VARCHAR, temp_record.mobilenumber::VARCHAR]; temp_iterator := temp_iterator + 1;

                                 END IF;
                END LOOP;

                out_status := 0;
                out_status_description := 'Message created successfully';
                out_value := temp_mobilenumbers;

                RETURN;                                                 

            END IF;         

        END IF;

    END IF;

END$$;