PLPGSQL array assignment not working, "array subscript in assignment must not be null"
- by Koen Schmeets
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$$;
