postgresql FUNCTION cannot detect the SELECT condition

Question

CREATE OR REPLACE FUNCTION heckANDinsert(
    first VARCHAR(20), surname VARCHAR(20),
  No VARCHAR(30), nation VARCHAR(30), dob DATE
) RETURNS SETOF passenger AS $$
DECLARE 

name VARCHAR(20);
Sname VARCHAR(20);
Pass VARCHAR(30);
nation VARCHAR(30);
birthdate DATE;

BEGIN

SELECT first, same,passportNo, nationality,dob 
INTO fname,sname, PassportN, nation, birthdate 
FROM passenger
WHERE 
    firstname = $1 
    AND surname = $2 
    AND passportNo = $3 
    AND nationality = $4 
    AND dob = $5;

IF NOT FOUND THEN
    INSERT INTO passenger 
    SELECT MAX(passengerid)+1, $1,$2,$3,$4,$5 
    FROM passenger;
ELSE 
    RAISE EXCEPTION 'passenger record already exists.';
END IF; 

END;
$$ LANGUAGE plpgsql;

I called the function using

SELECT passenger_checkANDinsert( 
    'john', 'levis', 'v39949455', 'British', '1990-07-23'
);

the function will insert the right result into database if i change the first name to another. but it won't work if i change the surname and remain the first name same as database data. i guess there is something wrong within the select statement WHERE condition in the function, but i can't find any error for that..

Answer 1

create or replace function passenger_checkandinsert(
    _firstname varchar(20), _surname varchar(20),
    _passportno varchar(30), _nationality varchar(30), _dob date
) returns setof passenger as $$

insert into passenger (
    passengerid, firstname, surname, passportNo, nationality, dob
)
select coalesce(max(passengerid)+1, 1), $1, $2, $3, $4, $5
from passenger
where not exists ( 
    select 1
    from passenger
    where
        firstname = $1
        and surname = $2
        and passportno = $3
        and nationality = $4
        and dob = $5
    )
returning *
;
$$ language sql;

• Using the name of columns in variables can be problematic. Just prepend an underline.
• Declare the columns you are inserting values into.
• Simple SQL will do all you need.
• Don't do count to check for the existence of a row as it will read the whole table. In instead use exists as it will stop reading in the first found row.
• Notice the coalesce to deal with the case of no rows yet.
• returning * will return the inserted row.

Answer 2

CREATE OR REPLACE FUNCTION passenger_checkANDinsert

(firstname VARCHAR(20),surname VARCHAR(20),

passportNo VARCHAR(30),nationality VARCHAR(30),dob DATE)

RETURNS SETOF passenger AS
$$

DECLARE 

_exists boolean;    

BEGIN

SELECT count(*)>0 INTO  _exists FROM passenger      
WHERE firstname = $1 AND surname = $2 AND passportNo = $3 AND nationality = $4 AND dob = $5;

IF NOT _exists THEN

    INSERT INTO passenger SELECT MAX(passengerid)+1, $1,$2,$3,$4,$5 FROM passenger;

    ELSE 

    RAISE EXCEPTION 'passenger record already exists.';

    END IF; 

END;

$$

   LANGUAGE plpgsql;

I think you are doing bad with MAX(passengerid)+1, why don't you use the bigserial data type ? If you are doing so just to not lose sequency of this field, you are not doing right.

Perhaps you need to add sequency to your field by:

CREATE SEQUENCE passengerid_seq;
ALTER TABLE passenger ALTER COLUMN passengerid SET DEFAULT NEXTVAL('passengerid_seq');