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.
-- Function: users_set(character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
DROP FUNCTION users_set(character varying, character varying, integer, integer, character varying, character varying, character varying, character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION users_set(
username character varying,
remember_token character varying,
p_usersid integer,
p_departmentid integer,
p_firstname character varying,
p_secondname character varying,
p_lastname character varying,
p_personal_number character varying,
p_position character varying,
p_userstatus character varying,
p_rolesid character varying)
RETURNS TABLE(r_rows integer, r_id integer, r_errcode character varying, r_errtext character varying) AS
$BODY$
DECLARE
id_user INT4 := NULL; -- id сотрудника;
PValues text[]; -- IN значение параметров
nP INT2 = 0; -- количество элеменотов в массиве
user_id int;
BEGIN
BEGIN
r_errCode := '0';
r_errText := '<Ok>';
id_user := user_permit_get(remember_token, username);
IF id_user IS NOT NULL THEN
PValues := string_to_array(p_rolesid, ',');
nP := array_length(PValues, 1);
-- добавление / изменние записи
BEGIN
IF ( p_usersid IS NULL ) THEN
RETURN QUERY
WITH TRW AS (
INSERT INTO users(
id_department_fk,
first_name,
second_name,
last_name,
personal_number,
position,
user_status,
created_at,
updated_at)
VALUES (
p_departmentid,
p_firstname,
p_secondname,
p_lastname,
p_personal_number,
p_position,
p_userstatus,
TO_CHAR(NOW(), 'YYYY-mm-dd HH24:MI:SS')::timestamp without time zone,
TO_CHAR(NOW(), 'YYYY-mm-dd HH24:MI:SS')::timestamp without time zone)
RETURNING *
)
SELECT CAST(COUNT(TRW.id_user) as INTEGER), CAST(TRW.id_user as INTEGER), CAST(r_errCode as character varying), CAST(r_errText as character varying)
FROM TRW
GROUP BY TRW.id_user, r_errCode, r_errText
;
ELSE
RETURN QUERY
WITH TRW AS (
UPDATE users
SET
id_department_fk = p_departmentid,
first_name = p_firstname,
second_name = p_secondname,
last_name = p_lastname,
personal_number = p_personal_number,
position = p_position,
user_status = p_userstatus,
created_at = TO_CHAR(NOW(), 'YYYY-mm-dd HH24:MI:SS')::timestamp without time zone,
updated_at = TO_CHAR(NOW(), 'YYYY-mm-dd HH24:MI:SS')::timestamp without time zone
WHERE users.id_user = p_usersid
RETURNING *
)
SELECT CAST(COUNT(TRW.id_user) as INTEGER), CAST(TRW.id_user as INTEGER), CAST(r_errCode as character varying), CAST(r_errText as character varying)
FROM TRW
GROUP BY TRW.id_user, r_errCode, r_errText;
END IF;
EXCEPTION
WHEN others THEN
RAISE EXCEPTION 'Ошибка выполнения: % %', SQLSTATE, SQLERRM;
END;
END IF;
EXCEPTION
WHEN others THEN
RAISE EXCEPTION 'Ошибка выполнения: % %', SQLSTATE, SQLERRM;
END;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION users_set(character varying, character varying, integer, integer, character varying, character varying, character varying, character varying, character varying, character varying, character varying)--;
OWNER TO gf;
--COMMENT ON FUNCTION users_set(character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character varying) IS 'Добавляет новую запись в таблицу users';
