postgresql:array & foreach

1
2
3
--数组:
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])[1]::text[];
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])::text[];
1
2
3
4
5
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[];

  

 

1
2
--select rows to array
SELECT ARRAY(select "Id" FROM ent."Enterprise" )
1
SELECT ARRAY(select "Id" FROM ent."Enterprise" )::int[]

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
--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$
1
2
3
4
5
6
7
8
9
10
11
12
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$

  

1
2
3
4
5
6
7
8
9
10
11
12
13
--一维数组
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$

   

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--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$

  

多维数组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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$;

  

  

  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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"='''' ;

  

  

 

  

  

posted @   PanPan003  阅读(4126)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?
点击右上角即可分享
微信分享提示