postgresql:array & foreach
--数组: SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])[1]::text[]; SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])::text[];
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])[1]::text[]; SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])::int[]; SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])[1][1]; SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1]; SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1]::int[];
--select rows to array SELECT ARRAY(select "Id" FROM ent."Enterprise" )
SELECT ARRAY(select "Id" FROM ent."Enterprise" )::int[]
--array foreach DO $do$ DECLARE m varchar[]; arr varchar[] := array[['key1','val1'],['key2','val2']]; BEGIN FOREACH m SLICE 1 IN ARRAY arr LOOP RAISE NOTICE 'another_func(%,%)',m[1], m[2]; END LOOP; END $do$
DO $do$ DECLARE m varchar[]; arr varchar[] := array['key1','val1']; BEGIN FOREACH m SLICE 1 IN ARRAY arr LOOP RAISE NOTICE 'another_func(%)',m; END LOOP; END $do$
--一维数组 DO $do$ DECLARE m int; arr int[] := ARRAY(select "Id" FROM ent."Enterprise" )::int[]; BEGIN FOREACH m IN ARRAY arr LOOP RAISE NOTICE 'another_func(%)',m; END LOOP; END $do$
--each insert from select DO $do$ DECLARE m int; arr int[] := ARRAY(select "Id" FROM "Enterprise" )::int[]; BEGIN FOREACH m IN ARRAY arr LOOP INSERT INTO "Wallet"("Id") VALUES (m); RAISE NOTICE 'another_func(%)',m; END LOOP; END $do$
多维数组
WITH data AS ( SELECT '{ {9,"12345"} , {9,aedrftgy} , {11,qwedrftgyh} ,{10,qertg} ,{12,qwedfg} ,{9,wedrftgh} ,{13,qwerftgh} ,{8,wertyu} }'::text[] AS arr ) SELECT arr[i][1] AS aspect, arr[i][2] AS preference FROM data, generate_subscripts((SELECT arr FROM data), 1) i ;
DO $do$ DECLARE r record; r2 record; n int; channelId integer; v_MaxId integer; platfromSettlementParty text; v_roles text[] := '{ {9,"zfhcnc''gdthfh"} , {9,123有限公司} , {11,3456有限公司} ,{10,467有限公司} ,{12,5678有限公司} ,{9,34567司} ,{13,3456公司} ,{8,23456公司} }'; BEGIN for r in ( WITH data AS ( SELECT v_roles::text[] AS arr ) SELECT * from (SELECT arr[i][1] AS aspect, arr[i][2] AS preference FROM data, generate_subscripts((SELECT arr FROM data), 1) i) t2 ) LOOP channelId=CAST(coalesce(r."aspect", '0') AS integer); platfromSettlementParty=r."preference"; Select max("Id")+1 into v_MaxId from ent."rrte"; IF NOT EXISTS(Select * from ent."rrte" where ent."rrte"."PlatfromSettlementParty"=platfromSettlementParty and ent."rrte"."ChannelId"=channelId limit 1) then INSERT INTO ent."rrte"("Id",""ChannelId", "PlatfromSettlementParty") VALUES (v_MaxId,channelId, platfromSettlementParty); end if; END LOOP; END; $do$;
DO $do$ DECLARE r record; r2 record; er record; eachr record; n int; ci integer; v_MaxId integer; v_pi integer; v_bi integer; v_ci integer; v_at integer; psp text; v_roles text[] := '{ {达有,9,百司,1,''} {顾问,8,上海,2,''} }'; BEGIN for r in ( WITH data AS ( SELECT v_roles::text[] AS arr ) SELECT arr[i][1] AS en, arr[i][2] AS ci, arr[i][3] AS psp,arr[i][4] AS at,arr[i][5] AS am FROM data, generate_subscripts((SELECT arr FROM data), 1) i ) LOOP psp= r."psp"; v_at=CAST(coalesce(r."at", '0') AS integer); Select max("Id")+1 into v_MaxId from ent."BA"; IF EXISTS(select "Id" from ent."A" as platform WHERE platform."PSP"=r."psp" limit 1)then select "Id" into v_pi from ent."A" as platform WHERE platform."PSP"=r."psp" limit 1; IF EXISTS(SELECT business."Id" FROM ent."B" as business INNER join ent."E" as enterprise on business."EI" = enterprise."Id" where business."BU"=2 and ( enterprise."N"=r."en" or enterprise."EN"=r."en") limit 1)then SELECT business."Id" into v_bi FROM ent."B" as business INNER join ent."E" as enterprise on business."BI" = enterprise."Id" where business."BU"=2 and ( enterprise."N"=r."en" or enterprise."EN"=r."en") limit 1; IF NOT EXISTS( SELECT account."Id", account."BI", account."PI", account."CI", account."AT", account."AM" FROM ent."BA" as account where account."BI"=v_bi and account."AI"=v_pi limit 1) then INSERT INTO ent."BA"("Id","BI", "AI", "AT", "AM") VALUES (v_MaxId, v_bI, v_pt, v_at,r."am"); ELSE UPDATE ent."BA" as account SET "AT"=v_at, "AM"=r."am" where account."BId"=v_bi and account."AId"=v_pi; end if; end if; end if; END LOOP; END; $do$; UPDATE ent."BA" SET "AM"='' where "AM"='''' ;