我有一个函数registration(),它应该在某些情况下向表添加一行.我已经包含了一段代码和来自通话的输出.
如果select *返回一个非空表行(它根据RAISE NOTICE执行),我想提出异常而不添加行.该示例似乎表明rowt不为null,但是rowt IS NOT NULL返回f(并且不会引发异常).
我希望这是我看不到的小事.
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;
IF rowt IS NOT NULL THEN
RAISE EXCEPTION 'email address,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;
从您的代码可以看出,您希望通过将电子邮件地址插入表中来注册电子邮件地址,但前提是电子邮件地址尚未注册并且提供了密码.对于初学者,您应该更改表定义以反映这些要求：
CREATE TABLE email (
id serial PRIMARY KEY,addr varchar(50) UNIQUE NOT NULL,passw varchar(50) NOT NULL,person_id integer
);
addr上的UNIQUE约束意味着PG不允许重复的电子邮件地址,因此您无需为此进行测试.您应该在执行插入时测试唯一的违规.
对于该功能,我建议您传递电子邮件地址和密码,而不是将业务逻辑放在函数中.像这样,该函数具有较少的依赖性,并且可以更容易地在其他上下文中重复使用(例如通过您的Web应用程序通过其他方式注册电子邮件地址).使函数STRICT确保pwd不为null,以便为您节省另一个测试.
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;
