Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 insert  [new]
neteurt
Member

Откуда:
Сообщений: 91
Здравствуйте, при добавление записи(insert), как мне получить значение айдишника который будет в TRW.id_user и присвоить ему переменную "user_id ", чтобы можно было использовать в дальнейшем по коду? покуда ничего не выходит
-- 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';
16 июл 20, 16:13    [22168709]     Ответить | Цитировать Сообщить модератору
 Re: insert  [new]
Swa111
Member

Откуда:
Сообщений: 202
neteurt,

может стоит разбить операцию return ... insert на две insert с сохранением результата в переменные, а затем return ... select


declare
  TRW record;
begin
  for trw in (                   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 *FROM TRW)
    loop
      --Здесь работаем с TRW.id_user 
    
      --проталкиваем код дальше
      RETURN QUERY 
      SELECT 1 /*CAST(COUNT(TRW.id_user) as INTEGER)? всегда 1?*/, 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 loop;
end;
16 июл 20, 22:04    [22168881]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить