place and route - Page 597

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$$;

Search Results

Search found 15578 results on 624 pages for 'place and route'.

Page 597/624 | < Previous Page | 593 594 595 596 597 598 599 600 601 602 603 604 | Next Page >

- by Koen Schmeets

- by Monomer

- by Neo

- by andrews

- by JasperS

- by three3

- by Holland

- by Jayshree

- by codevour

- by StealthRT

- by inflector

- by Michael La Voie

- by DGGenuine

- by Matt Rogish

- by Aurélien Momow

- by Odette

- by Hadi Eskandari

- by Panther24

- by Pete Duncanson

- by prometheus

- by prometheus

- by Wim Haanstra

- by ollie

- by Az

- by Kelso

< Previous Page | 593 594 595 596 597 598 599 600 601 602 603 604 | Next Page >