--数组：

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