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"='''' ;