4赞

当前位置: 开发笔记 > 数据库 > 正文

当没有记录时,FOUND变量设置为"true"

作者：k78283381 | 2023-09-07 17:13

如何解决《当没有记录时,FOUND变量设置为"true"》经验，为你挑选了1个好方法。

在PLPGSQL函数中,我有一些行来检查记录是否存在.好吧,即使没有记录,FOUND变量也会设置为TRUE.非常奇怪的事情.

我正在测试的行是:

query:='SELECT i.identity_id FROM app.identity as i,' || quote_ident(schema_name) || '.users as u,' || quote_ident(schema_name) || '.udata as ud WHERE i.identity_id=' || p_identity_id || ' and u.identity_id=i.identity_id and ud.identity_id=i.identity_id';
        RAISE WARNING 'query=%',query;
        PERFORM query;
        RAISE WARNING 'FOUND=%',FOUND;
        IF FOUND 
        THEN
            RAISE WARNING 'Record found, rising an exception';
            RAISE EXCEPTION USING ERRCODE = 'AA002';
        END IF;

这是输出:

WARNING:  query=SELECT i.identity_id FROM app.identity as i,"comp-1049007476".users as u,"comp-1049007476".udata as ud WHERE i.identity_id=-1615382132 and u.identity_id=i.identity_id and ud.identity_id=i.identity_id
WARNING:  FOUND=t
WARNING:  Record found, rising an exception

这是pg_log:

 2017-01-09 09:47:21.906 CST > LOG:  execute : SELECT app.create_identity($1::varchar,'') as identity_id
< 2017-01-09 09:47:21.906 CST > DETAIL:  parameters: $1 = 'someuser@domain.com'
< 2017-01-09 09:47:21.908 CST > WARNING:  new_identity_id=-1615382132
< 2017-01-09 09:47:21.908 CST > CONTEXT:  PL/pgSQL function app.create_identity(character varying,character varying) line 18 at RAISE
< 2017-01-09 09:47:21.917 CST > LOG:  execute : select app.add_user(       
                        $1::integer,
                        $2::varchar,                     
                        $3::integer,                     
                        $4::varchar,                     
                        $5::varchar,                     
                        $6::varchar,                     
                        $7::varchar,                     
                        $8::varchar,                     
                        $9::varchar,                     
                        $10::varchar,                    
                        $11::integer,                    
                        $12::integer,                    
                        $13::integer,                    
                        $14::integer,                    
                        $15::integer,                    
                        $16::integer,                    
                        $17::smallint,                   
                        $18::boolean,                    
                        $19::boolean,                    
                        $20::boolean,                    
                        $21::boolean,                    
                        $22::boolean                     
                        )
< 2017-01-09 09:47:21.917 CST > DETAIL:  parameters: $1 = '905220468', $2 = '763715373817831', $3 = '-1049007476', $4 = 'Some User', $5 = '44444', $6 = '', $7 = '', $8 = '', $9 = '', $10 = '', $11 = '-1615382132', $12 = '0', $13 = '1', $14 = '0', $15 = '0', $16 = '0', $17 = '0', $18 = 't', $19 = 'f', $20 = 'f', $21 = 'f', $22 = 'f'
< 2017-01-09 09:47:21.919 CST > WARNING:  query=SELECT i.identity_id FROM app.identity as i,"comp-1049007476".users as u,"comp-1049007476".udata as ud WHERE i.identity_id=-1615382132 and u.identity_id=i.identity_id and ud.identity_id=i.identity_id
< 2017-01-09 09:47:21.919 CST > CONTEXT:  PL/pgSQL function app.add_user(integer,character varying,integer,character varying,character varying,character varying,character varying,character varying,character varying,character varying,integer,integer,integer,integer,integer,integer,smallint,boolean,boolean,boolean,boolean,boolean) line 25 at RAISE
< 2017-01-09 09:47:21.919 CST > WARNING:  FOUND=t
< 2017-01-09 09:47:21.919 CST > CONTEXT:  PL/pgSQL function app.add_user(integer,character varying,integer,character varying,character varying,character varying,character varying,character varying,character varying,character varying,integer,integer,integer,integer,integer,integer,smallint,boolean,boolean,boolean,boolean,boolean) line 27 at RAISE
< 2017-01-09 09:47:21.919 CST > WARNING:  Record found, rising an exception
< 2017-01-09 09:47:21.919 CST > CONTEXT:  PL/pgSQL function app.add_user(integer,character varying,integer,character varying,character varying,character varying,character varying,character varying,character varying,character varying,integer,integer,integer,integer,integer,integer,smallint,boolean,boolean,boolean,boolean,boolean) line 30 at RAISE
< 2017-01-09 09:47:21.919 CST > ERROR:  Identity already registered

为什么我确定记录不存在？好吧,我把我知道的值放在表中,但为了以防万一,我验证:

dev=> SELECT i.identity_id FROM app.identity as i,"comp-1049007476".users as u,"comp-1049007476".udata as ud WHERE i.identity_id=-1615382132 and u.identity_id=i.identity_id and ud.identity_id=i.identity_id;
 identity_id 
-------------
(0 rows)

dev=>

见,0记录.所以,大问题是,为什么FOUND变量谎言？这里发生了什么？

这是我的函数的完整代码:

CREATE OR REPLACE FUNCTION app.add_user(sess_identity_id int,session_str varchar,sess_company_id int,
    p_full_name         varchar,
    p_mob_phone         varchar,
    p_home_phone        varchar,
    p_work_phone        varchar,
    p_phone_ext         varchar,
    p_position          varchar,
    p_notes             varchar,
    p_identity_id       integer,
    p_user_id           integer,
    p_group_id          integer,
    p_shift_id          integer,
    p_boss_id           integer,
    p_crreated_by       integer,
    p_timezone          smallint,
    p_utype             boolean,
    p_create_root       boolean,
    p_has_subord        boolean,
    p_inactive          boolean,
    p_generic           boolean 
)
RETURNS integer as $$
DECLARE
    v_session                   bigint;
    schema_name                 varchar;
    query                       varchar;
    current_ts                  integer;
    v_user_id                   integer;
    v_generic                   integer;
    v_udata_id                  integer;
    _c                          text;
BEGIN
        SELECT extract(epoch from now())::integer into current_ts;
        SELECT session_str::bigint INTO v_session;
        schema_name:='comp' || sess_company_id;

        IF NOT EXISTS (
            SELECT 1 FROM app.session WHERE app.session.identity_id=sess_identity_id AND app.session.session=v_session
        ) THEN
            RAISE EXCEPTION USING ERRCODE = 'AA001';
        END IF;

        query:='SELECT i.identity_id FROM app.identity as i,' || quote_ident(schema_name) || '.users as u,' || quote_ident(schema_name) || '.udata as ud WHERE i.identity_id=' || p_identity_id || ' and u.identity_id=i.identity_id and ud.identity_id=i.identity_id';
        RAISE WARNING 'query=%',query;
        PERFORM query;
        RAISE WARNING 'FOUND=%',FOUND;
        IF FOUND 
        THEN
            RAISE WARNING 'Record found, rising an exception';
            RAISE EXCEPTION USING ERRCODE = 'AA002';
        END IF;

        query:='SELECT u.user_id,u.generic FROM ' || quote_ident(schema_name) || '.users as u,identity as i WHERE i.email=p_email AND i.identity_id=u.identity_id';
        RAISE WARNING 'query=%',query;
        EXECUTE query INTO v_user_id,v_generic;
        IF NOT FOUND
        THEN
            EXECUTE 'INSERT INTO $1.users(
                identity_id,
                group_id,
                shift_id,
                boss_id,
                created_by,
                date_inserted
                timezone,
                utype,
                create_root,
                has_subord,
                inactive,
                generic
            ) VALUES ($2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING user_id' 
                INTO v_user_id 
                USING schema_name,p _identity_id,p_group_id,p_shift_id,p_boss_id,p_created_by,p_date_inserted,p_timezone,p_utype,p_create_root,p_has_subord,p_inactive,p_generc;
        END IF;
        if NOT v_generic THEN
            RAISE EXCEPTION USING ERRCODE = 'AA003';
        END IF;
        EXECUTE 'INSERT INTO $1.udata(user_id,identity_id,date_insert,full_name,mob_phone,home_phone,work_phone,phone_ext,position,notes) VALUES ($2,$3,$4,$5,$6,$7,$8,$9,$10,$11)'
                INTO v_udata_id
                USING schema_name,v_user_id,p_identity_id,current_ts,p_full_name,p_mob_phone,p_home_phone,p_work_phone,p_phone_ext,p_position,p_notes;
        RETURN v_udata_id;
EXCEPTION
    WHEN SQLSTATE 'AA001' THEN
        RAISE EXCEPTION USING ERRCODE = 'AA001', message = 'Invalid session';
    WHEN SQLSTATE 'AA002' THEN
        RAISE EXCEPTION USING ERRCODE = 'AA002', message = 'Identity already registered';
    WHEN SQLSTATE 'AA003' THEN
        RAISE EXCEPTION USING ERRCODE = 'AA003', message = 'Not a generic user, can not add email address';
    WHEN OTHERS THEN
        RAISE NOTICE 'add_user() failed with... error: % %',SQLSTATE,SQLERRM;
        GET STACKED DIAGNOSTICS _c = PG_EXCEPTION_CONTEXT;
        RAISE NOTICE 'context: >>%<<', _c;
        RAISE EXCEPTION USING MESSAGE = 'An error which is not handled by function';
END
$$ LANGUAGE plpgsql;

和表结构,以防万一:

dev=> \d+ app.identity;
                                    Table "app.identity"
    Column     |          Type          | Modifiers | Storage  | Stats target | Description 
---------------+------------------------+-----------+----------+--------------+-------------
 identity_id   | integer                | not null  | plain    |              | 
 created_by    | integer                |           | plain    |              | 
 email         | character varying(128) |           | extended |              | 
 date_inserted | integer                |           | plain    |              | 
 password      | character varying(32)  |           | extended |              | 
 validated     | smallint               |           | plain    |              | 
Indexes:
    "identity_pkey" PRIMARY KEY, btree (identity_id)
    "identity_email_key" UNIQUE CONSTRAINT, btree (email)

dev=> \d+ "comp-1049007476".users;
                                                        Table "comp-1049007476.users"
    Column     |   Type   |                                 Modifiers                                 | Storage | Stats target | Description 
---------------+----------+---------------------------------------------------------------------------+---------+--------------+-------------
 user_id       | integer  | not null default nextval('"comp-1049007476".users_user_id_seq'::regclass) | plain   |              | 
 identity_id   | integer  | default 0                                                                 | plain   |              | 
 group_id      | integer  | default 0                                                                 | plain   |              | 
 shift_id      | integer  | default 0                                                                 | plain   |              | 
 boss_id       | integer  | default 0                                                                 | plain   |              | 
 created_by    | integer  | default 0                                                                 | plain   |              | 
 date_inserted | integer  | default 0                                                                 | plain   |              | 
 timezone      | smallint | default 0                                                                 | plain   |              | 
 utype         | boolean  | default false                                                             | plain   |              | 
 create_root   | boolean  | default false                                                             | plain   |              | 
 has_subord    | boolean  | default false                                                             | plain   |              | 
 inactive      | boolean  | default false                                                             | plain   |              | 
 generic       | boolean  | default false                                                             | plain   |              | 

dev-> \d+ "comp-1049007476".udata 
                                                                Table "comp-1049007476.udata"
    Column     |          Type          |                                 Modifiers                                  | Storage  | Stats target | Description 
---------------+------------------------+----------------------------------------------------------------------------+----------+--------------+-------------
 udata_id      | integer                | not null default nextval('"comp-1049007476".udata_udata_id_seq'::regclass) | plain    |              | 
 user_id       | integer                | default 0                                                                  | plain    |              | 
 identity_id   | integer                | default 0                                                                  | plain    |              | 
 date_inserted | integer                | default 0                                                                  | plain    |              | 
 full_name     | character varying(64)  | default ''::character varying                                              | extended |              | 
 mob_phone     | character varying(16)  | default ''::character varying                                              | extended |              | 
 home_phone    | character varying(16)  | default ''::character varying                                              | extended |              | 
 work_phone    | character varying(16)  | default ''::character varying                                              | extended |              | 
 phone_ext     | character varying(8)   | default ''::character varying                                              | extended |              | 
 position      | character varying(64)  | default ''::character varying                                              | extended |              | 
 notes         | character varying(128) | default ''::character varying                                              | extended |              | 

dev->

Pavel Stehul.. 6

基本问题在于使用PERFORM陈述.此语句专为无需结果处理的功能评估而设计.它不是为动态SQL设计的(其中SQL以字符串形式输入).

PLpgSQL几乎将所有语句翻译成SELECTs.PERFORM也不例外.

PERFORM fx(10,20);

被翻译成:

SELECT fx(10,20);

你的代码:

variable := 'SELECT * FROM foo';
PERFORM variable;

被翻译成:

SELECT 'SELECT * FROM foo'; -- it is same like SELECT 'hello';

此查询返回一行,变量FOUND应为true.所以没有什么是奇怪的.

在PLpgSQL中,您应该在静态SQL和动态SQL之间存在很大差异.动态SQL(在运行时汇编查询)仅受EXECUTE语句支持.

用法PERFORM variable是对此语句的简单错误使用 - 但它生成有效的SQL语句,可以在没有运行时错误的情况下进行评估.

1> Pavel Stehul..：

基本问题在于使用PERFORM陈述.此语句专为无需结果处理的功能评估而设计.它不是为动态SQL设计的(其中SQL以字符串形式输入).

PLpgSQL几乎将所有语句翻译成SELECTs.PERFORM也不例外.

PERFORM fx(10,20);

被翻译成:

SELECT fx(10,20);

你的代码:

variable := 'SELECT * FROM foo';
PERFORM variable;

被翻译成:

SELECT 'SELECT * FROM foo'; -- it is same like SELECT 'hello';

此查询返回一行,变量FOUND应为true.所以没有什么是奇怪的.

在PLpgSQL中,您应该在静态SQL和动态SQL之间存在很大差异.动态SQL(在运行时汇编查询)仅受EXECUTE语句支持.

用法PERFORM variable是对此语句的简单错误使用 - 但它生成有效的SQL语句,可以在没有运行时错误的情况下进行评估.

当没有记录时,FOUND变量设置为"true"

按条件跨多个列交换值

如何在matplotlib中更改科学记数法的字体大小？

解决错误“与返回的局部变量关联的堆栈内存地址”

Windows命令：如何使用chdir推送当前目录以便以后弹出？

ObjectResult <T>不可用的异步方法

如何在java中获取Path的长度？

在cx_Oracle中使用with cursor on cursor

关于返回STL容器数据成员而不调用复制构造函数

brew install opencv3显然是成功的,但缺少cv2.so和cv2.py

在派生类中,如何从基类中的属性类型获取派生类型的属性？

R中coord_flip后的反转顺序

如何使用Jupyter笔记本进行彩色打印

如何使用Hibernate和Spring Boot启用批量插入

无法通过PHP查询MySQL数据库

使用Moment.js获取序号

DataTampger中DataConmplate内部控件的Visibility无法正常工作

我是否通过将我的主目录设为回购,然后在其中包含其他回购来滥用git？

如何使用angularjs在复选框列表中推送选定的值

修改strace以捕获并替换打开的系统调用中的文件名

Make使线程安全