-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPostgreSQL functions.txt
155 lines (115 loc) · 3.46 KB
/
PostgreSQL functions.txt
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
CREATE OR REPLACE FUNCTION public.fnGetAllUserRoles()
RETURNS TABLE(frRoleID uuid, fmRoleNumber int, fmRoleName character varying)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
RETURN QUERY
SELECT roleid, rolenumber, rolename
FROM public.tblroles where roleisdeleted=0;
END;
$BODY$;
ALTER FUNCTION public.fnGetAllUserRoles()
OWNER TO Postgres;
SELECT public.fnGetAllUserRoles();
SELECT * from fnGetAllUserRoles();
CREATE OR REPLACE FUNCTION public.fnGetSeletedUserRole(selectedRoleID UUID)
RETURNS TABLE(frRoleID uuid, fmRoleNumber int, fmRoleName character varying)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
RETURN QUERY
SELECT roleid, rolenumber, rolename
FROM public.tblroles where roleisdeleted=0 and roleid=selectedRoleID;
END;
$BODY$;
ALTER FUNCTION public.fnGetSeletedUserRole(selectedRoleID UUID)
OWNER TO Postgres;
select * from public.fnGetSeletedUserRole('68419044-9103-439c-a3de-033cc8ba45b3');
CREATE OR REPLACE FUNCTION public.fnGetAllUserAccount()
RETURNS TABLE(
fmusrid uuid,
fmusrnumber int,
fmusrfirstname character varying,
fmusrlastname character varying,
fmusrknownas character varying,
fmusraddress character varying,
fmusrprimaryemail character varying,
fmusrsecondaryemail character varying,
fmusrmobile character varying,
fmusrwhatsapp character varying,
fmusrlandphone character varying,
fmusrmfaactivated int,
fmusrssoactivated int,
fmusrnotes character varying,
fmusrisdeleted int,
fmusrroledid uuid ,
fmusrcreatedon timestamp ,
fmusrlastupdatedon timestamp ,
fmusrcomments character varying
)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
RETURN QUERY
SELECT usrid, usrnumber, usrfirstname, usrlastname, usrknownas,
usraddress, usrprimaryemail, usrsecondaryemail, usrmobile,
usrwhatsapp, usrlandphone, usrmfaactivated, usrssoactivated,
usrnotes, usrisdeleted, usrroledid, usrcreatedon, usrlastupdatedon, usrcomments
FROM public.tblusers where usrisdeleted=0;
END;
$BODY$;
ALTER FUNCTION public.fnGetAllUserAccount()
OWNER TO Postgres;
SELECT public.fnGetAllUserAccount();
SELECT * from fnGetAllUserAccount();
CREATE OR REPLACE FUNCTION public.fnGetSelectedUserAccount(selectedUserID UUID)
RETURNS TABLE(
fmusrid uuid,
fmusrnumber int,
fmusrfirstname character varying,
fmusrlastname character varying,
fmusrknownas character varying,
fmusraddress character varying,
fmusrprimaryemail character varying,
fmusrsecondaryemail character varying,
fmusrmobile character varying,
fmusrwhatsapp character varying,
fmusrlandphone character varying,
fmusrmfaactivated int,
fmusrssoactivated int,
fmusrnotes character varying,
fmusrisdeleted int,
fmusrroledid uuid ,
fmusrcreatedon timestamp ,
fmusrlastupdatedon timestamp ,
fmusrcomments character varying
)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
RETURN QUERY
SELECT usrid, usrnumber, usrfirstname, usrlastname, usrknownas,
usraddress, usrprimaryemail, usrsecondaryemail, usrmobile,
usrwhatsapp, usrlandphone, usrmfaactivated, usrssoactivated,
usrnotes, usrisdeleted, usrroledid, usrcreatedon, usrlastupdatedon, usrcomments
FROM public.tblusers where usrisdeleted=0 and usrid=selectedUserID;
END;
$BODY$;
ALTER FUNCTION public.fnGetSelectedUserAccount(selectedUserID UUID)
OWNER TO Postgres;