Why is IS NOT NULL false when checking a row type?

Question

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.

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;

Output:

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;

Answer 1

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
);

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.

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;