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" = '' '' ; |
分类:
SQL
标签:
postgresql
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?