时间戳

首页 > 解决方案 > 用于插入记录的 PostgreSQL 函数未按预期工作

postgresql - 用于插入记录的 PostgreSQL 函数未按预期工作

问题描述

如果用户不存在，我正在尝试创建一个函数来创建用户（将记录插入表 USERS）。我正在运行的 PostgreSQL 版本是：

PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

表 USERS 具有以下列：

id serial primary key
,username text
,password_hash text

我创建了一个函数，它将返回给定用户名的用户 ID。如果用户名不存在，则返回 0，否则将返回有效的用户 ID。这个函数（在我的 USER_DATA 模式中）如下：

CREATE OR REPLACE FUNCTION user_data.get_user_id(
    p_username text)
RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE user_id int;
BEGIN

    IF exists(select distinct id from user_data.users where username = p_username) THEN
        SELECT distinct id INTO user_id
        FROM user_data.users
        WHERE username = p_username;
    ELSE
        user_id = 0;
    END IF;

    RETURN user_id;
END;
$BODY$;

如果 GET_USER_ID 返回 0，我现在正在研究一个创建用户的函数。三个可能的返回值是：

TRUE - if user is created
FALSE - if username already exists and user is not created
ERROR - if there is any type of error

测试此功能时，这些是我的结果：

当用户已经存在时运行：FALSE <--这是预期的

在用户不存在时运行：错误 <-- 这不是预期的。

任何人都可以提供有关为什么我可能会在以下逻辑中出现错误的见解：

CREATE OR REPLACE FUNCTION user_data.create_user(in p_username text, p_password_hash text)
returns text AS $$
DECLARE 
    v_user_created text;
    v_user_id int;
    v_error text;

BEGIN
    select user_data.get_user_id(p_username) into v_user_id;

    IF v_user_id = 0 THEN

        insert into user_data.users(username, password_hash) values (p_username, p_password_hash);
        commit;
        v_user_created := 'TRUE';
    ELSE
        v_user_created := 'FALSE';
    END IF;

    RETURN v_user_created;

    EXCEPTION WHEN OTHERS THEN
    BEGIN
    v_error := 'ERROR';
    RETURN v_error;
    END;
END;
$$ LANGUAGE plpgsql;



select user_data.create_user('new_user', 'sample_password_not_hashed');

非常感谢任何帮助、见解或批评。

标签： postgresqlplpgsql

解决方案

COMMIT里面create_user不是必须的。

IF v_user_id = 0 THEN
    insert into users(username, password_hash) values (p_username, p_password_hash);
    --commit;
    v_user_created := 'TRUE';
ELSE
    v_user_created := 'FALSE';
END IF;

db<>小提琴演示

推荐阅读