为什么在检查行类型时IS NOT NULL为false? [英] Why is IS NOT NULL false when checking a row type?

问题描述

我有一个函数registration()，应该在某些情况下向表中添加一行.我已经包含了一段代码和一个呼叫的输出.

I have a function registration(), which is supposed to add a row to a table under certain circumstances. I've included a snippet of code and output from a call.

如果select *返回一个非空的表行(根据RAISE NOTICE这样做)，我想引发异常，而不是添加该行.该示例似乎表明rowt不为null，但是rowt IS NOT NULL返回f(并且不会引发异常).

If select * returns a non-empty table row (which it does according to the RAISE NOTICE) I want to raise the exception and not add the row. The example seems to show that rowt is not null, and yet rowt IS NOT NULL returns f (and the exception is not raised).

我希望这是我看不到的小事.

I hope this is something minor I'm not seeing.

select * into rowt from Email where email_email = eml;
RAISE NOTICE '%, rowt IS NOT NULL:%',rowt, rowt IS NOT NULL;
if rowt IS NOT NULL THEN
   RAISE EXCEPTION 'email address, %, already registered.' , eml;
END IF;

输出:

NOTICE:  (7,,,), rowt IS NOT NULL:f

registration 
--------------
    21
(1 row)

CREATE TABLE IF NOT EXISTS Email ( 
   email_email VARCHAR(50) NOT NULL, 
   email_password VARCHAR(50) NOT NULL,
   email_id integer DEFAULT nextval('email_email_id_seq'::regclass) NOT NULL,
   email_person_id integer
);
CREATE OR REPLACE FUNCTION registration( wr text ) RETURNS integer AS $rL$
DECLARE
    eml text;
    pwd text;
    nm text;
    rle text;
    emid integer;
    rowt Email%ROWTYPE;
BEGIN
    eml := getWebVarValue( wr , 'email' );
    select * into rowt from Email where email_email = eml;
    RAISE NOTICE '%, rowt IS NOT NULL:%', rowt, rowt IS NOT NULL;
    IF rowt IS NOT NULL THEN
       RAISE EXCEPTION 'email address, %, already registered.' , eml;
    END IF;
    pwd := getWebVarValue( wr , 'password' );
    IF pwd IS NULL THEN
       RAISE EXCEPTION 'No password specified in registration.';
    END IF;
    INSERT INTO Email VALUES (eml,pwd) RETURNING Email.email_id INTO emid;
    --nm =  getWebVarValue( wr , 'name' );
    --rle = getWebVarValue( wr , 'role' );
    RETURN emid;
END;
$rL$ LANGUAGE plpgsql;

推荐答案

根据您的代码，您希望通过将其插入表格中来注册电子邮件地址，但前提是该电子邮件地址尚未注册且提供密码.对于初学者，您应该更改表定义以反映这些要求:

From your code it follows that you want to register an email address by inserting it in a table, but only if the email address isn't already registered and a password is supplied. For starters, you should change your table definition to reflect those requirements:

CREATE TABLE email ( 
    id        serial PRIMARY KEY,
    addr      varchar(50) UNIQUE NOT NULL, 
    passw     varchar(50) NOT NULL,
    person_id integer
);

addr上的UNIQUE约束意味着PG将不允许重复的电子邮件地址，因此您不必对此进行测试.相反，您应该在插入时测试是否存在唯一的违规行为.

The UNIQUE constraint on addr means that PG will not allow duplicate email addresses so you don't have to test for that. You should instead test for a unique violation when doing the insert.

对于功能，我建议您输入电子邮件地址和密码，而不要将业务逻辑放入功能中.这样，该函数具有较少的依赖性，并且可以更轻松地在其他上下文中重用(例如，通过Web应用程序通过其他某种方式注册电子邮件地址).设置函数STRICT可以确保pwd不为null，从而为您节省了另一项测试.

For the function I suggest you pass in the email address and password, instead of putting the business logic inside the function. Like this the function has fewer dependencies and can be re-used in other contexts more easily (such as registering an email address via some other means via your web app). Making the function STRICT ensures that pwd is not null so that saves you another test.

CREATE OR REPLACE FUNCTION registration(eml text, pwd text) RETURNS integer AS $rL$
DECLARE
    emid integer;
BEGIN
    INSERT INTO email (addr, passw) VALUES (eml, pwd) RETURNING id INTO emid;
    RETURN emid;
EXCEPTION
    WHEN unique_violation THEN
        RAISE 'Email address % already registered', eml;
        RETURN NULL;
END;
$rL$ LANGUAGE plpgsql STRICT;

这篇关于为什么在检查行类型时IS NOT NULL为false?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！