DO
B
E
G
I
N
D
E
C
L
A
R
E
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
r
e
f
c
u
r
s
o
r
;
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
2
r
e
f
c
u
r
s
o
r
;
s
q
l
s
t
r
V
A
R
C
H
A
R
(
1000
)
;
d
e
v
i
c
e
i
d
I
N
T
;
u
p
d
a
t
e
t
i
m
e
t
i
m
e
s
t
a
m
p
w
i
t
h
o
u
t
t
i
m
e
z
o
n
e
;
e
n
d
s
t
a
t
e
t
i
m
e
t
i
m
e
s
t
a
m
p
w
i
t
h
o
u
t
t
i
m
e
z
o
n
e
=
c
a
s
t
(
′
0001
−
01
−
0100
:
00
:
0
0
′
a
s
t
i
m
e
s
t
a
m
p
w
i
t
h
o
u
t
t
i
m
e
z
o
n
e
)
;
B
E
G
I
N
O
P
E
N
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
F
O
R
E
X
E
C
U
T
E
′
s
e
l
e
c
t
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
.
d
e
v
i
c
e
i
d
f
r
o
m
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
g
r
o
u
p
b
y
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
.
d
e
v
i
c
e
i
d
′
;
l
o
o
p
F
E
T
C
H
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
I
N
T
O
d
e
v
i
c
e
i
d
;
I
F
F
O
U
N
D
T
H
E
N
O
P
E
N
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
2
F
O
R
E
X
E
C
U
T
E
′
s
e
l
e
c
t
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
.
u
p
d
a
t
e
t
i
m
e
f
r
o
m
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
w
h
e
r
e
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
.
d
e
v
i
c
e
i
d
=
′
∣
∣
d
e
v
i
c
e
i
d
∣
∣
′
o
r
d
e
r
b
y
u
p
d
a
t
e
t
i
m
e
d
e
s
c
′
;
l
o
o
p
F
E
T
C
H
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
2
I
N
T
O
u
p
d
a
t
e
t
i
m
e
;
I
F
F
O
U
N
D
T
H
E
N
s
q
l
s
t
r
=
′
u
p
d
a
t
e
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
s
e
t
e
n
d
s
t
a
t
e
t
i
m
e
=
′
′
′
∣
∣
e
n
d
s
t
a
t
e
t
i
m
e
∣
∣
′
′
′
w
h
e
r
e
o
l
d
e
v
i
c
e
d
o
w
n
t
i
m
e
.
d
e
v
i
c
e
i
d
=
′
∣
∣
d
e
v
i
c
e
i
d
∣
∣
′
a
n
d
u
p
d
a
t
e
t
i
m
e
=
′
′
′
∣
∣
u
p
d
a
t
e
t
i
m
e
∣
∣
′
′
′
′
;
E
X
E
C
U
T
E
s
q
l
s
t
r
;
e
n
d
s
t
a
t
e
t
i
m
e
=
u
p
d
a
t
e
t
i
m
e
;
E
L
S
E
e
x
i
t
;
E
N
D
I
F
;
E
N
D
l
o
o
p
;
e
n
d
s
t
a
t
e
t
i
m
e
=
c
a
s
t
(
′
0001
−
01
−
0100
:
00
:
0
0
′
a
s
t
i
m
e
s
t
a
m
p
w
i
t
h
o
u
t
t
i
m
e
z
o
n
e
)
;
c
l
o
s
e
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
2
;
E
L
S
E
e
x
i
t
;
E
N
D
I
F
;
E
N
D
l
o
o
p
;
c
l
o
s
e
u
n
b
o
u
n
d
r
e
f
c
u
r
s
o
r
;
E
N
D
;
E
N
D
;
BEGIN DECLARE unbound_refcursor refcursor ; unbound_refcursor2 refcursor ; sqlstr VARCHAR (1000) ; deviceid INT ; updatetime timestamp without time zone ; endstatetime timestamp without time zone = cast ('0001-01-01 00:00:00' as timestamp without time zone); BEGIN OPEN unbound_refcursor FOR EXECUTE 'select oldevicedowntime.deviceid from oldevicedowntime group by oldevicedowntime.deviceid' ; loop FETCH unbound_refcursor INTO deviceid ; IF FOUND THEN OPEN unbound_refcursor2 FOR EXECUTE 'select oldevicedowntime.updatetime from oldevicedowntime where oldevicedowntime.deviceid='||deviceid||' order by updatetime desc '; loop FETCH unbound_refcursor2 INTO updatetime; IF FOUND THEN sqlstr = 'update oldevicedowntime set endstatetime='''||endstatetime||''' where oldevicedowntime.deviceid = '||deviceid||' and updatetime='''||updatetime||''''; EXECUTE sqlstr; endstatetime = updatetime ; ELSE exit ; END IF ; END loop ; endstatetime= cast ('0001-01-01 00:00:00' as timestamp without time zone); close unbound_refcursor2; ELSE exit ; END IF ; END loop ; close unbound_refcursor; END ; END ;
BEGINDECLAREunboundrefcursorrefcursor;unboundrefcursor2refcursor;sqlstrVARCHAR(1000);deviceidINT;updatetimetimestampwithouttimezone;endstatetimetimestampwithouttimezone=cast(′0001−01−0100:00:00′astimestampwithouttimezone);BEGINOPENunboundrefcursorFOREXECUTE′selectoldevicedowntime.deviceidfromoldevicedowntimegroupbyoldevicedowntime.deviceid′;loopFETCHunboundrefcursorINTOdeviceid;IFFOUNDTHENOPENunboundrefcursor2FOREXECUTE′selectoldevicedowntime.updatetimefromoldevicedowntimewhereoldevicedowntime.deviceid=′∣∣deviceid∣∣′orderbyupdatetimedesc′;loopFETCHunboundrefcursor2INTOupdatetime;IFFOUNDTHENsqlstr=′updateoldevicedowntimesetendstatetime=′′′∣∣endstatetime∣∣′′′whereoldevicedowntime.deviceid=′∣∣deviceid∣∣′andupdatetime=′′′∣∣updatetime∣∣′′′′;EXECUTEsqlstr;endstatetime=updatetime;ELSEexit;ENDIF;ENDloop;endstatetime=cast(′0001−01−0100:00:00′astimestampwithouttimezone);closeunboundrefcursor2;ELSEexit;ENDIF;ENDloop;closeunboundrefcursor;END;END;
DO
$$
BEGIN
declare
unbound_refcursor refcursor;
strDeviceid varchar(1000);
userid uuid;
tablename varchar(1000);
strSql varchar(1000);
_val varchar(1000);
begin
open unbound_refcursor for
select id from cfg_user;
loop
fetch unbound_refcursor into userid;
if found then
tablename=replace(CAST(userid AS varchar),’-’,’’);
RAISE NOTICE 'userid-- %',userid;
RAISE NOTICE 'tablename-- %',tablename;
strSql=' create materialized view AssetTree_'||tablename||' as
(WITH RECURSIVE AssetTree AS(
SELECT A.*,b.pid
FROM
com_asset A
INNER JOIN com_assetrela b ON A . ID = b.cid
WHERE
b.grouptype = 0
and a.id in (select assetid from cfg_userasset where userid =’’’||userid||’’’)
UNION ALL
SELECT
C.*,
d.pid
FROM
com_asset C
INNER JOIN com_assetrela d ON C . ID = d.cid
INNER JOIN AssetTree ON AssetTree.ID = d.pid
AND d.cid<> d.pid ) select * from AssetTree)';
execute strSql;
else
exit;
end if;
end loop;
end;
END;
$$
2750
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
