Here's script what gives details of case: When last row is executed, it gives as result 1 when it should give 18 I have checked that auid comes to function correctly, seems that SELECT in function doesn't use WHERE auid=$1 PostgreSQL RPM version: postgresql-8.0.5-1.FC4.1 arch i386 Here is the sql: -------------------- DROP TABLE admins2; CREATE TABLE admins2 ( auid INTEGER UNIQUE, name VARCHAR(64) ); INSERT INTO admins2 VALUES (1,'name1'); INSERT INTO admins2 VALUES (18,'name2'); DROP FUNCTION testf (auid INT); CREATE FUNCTION testf (auid INT) RETURNS INTEGER AS $$ DECLARE admin_rec RECORD; BEGIN SELECT INTO admin_rec * FROM admins2 WHERE auid=$1; IF admin_rec IS NOT NULL THEN RETURN admin_rec.auid; END IF; RETURN 'not'; END; $$ LANGUAGE plpgsql; SELECT testf(auid) FROM admins2 WHERE auid = 18;
Sorry, this is just pilot error. You used auid as a parameter alias, so the SELECT command effectively has WHERE $1=$1, which means admin_rec always gets the first row of admins2. It's a good rule of thumb never to use any variable or parameter names in a function that are identical to names of fields or tables you need to access in that function; otherwise both you and the machine are likely to get confused. BTW, I don't think IS NOT NULL on a RECORD variable will do what you expect, either. You probably want to write IF FOUND THEN there.