Bug 176497

Summary:	PostgreSQL plpgsql function bug
Product:	[Fedora] Fedora	Reporter:	Tomi Malkki <tomi>
Component:	postgresql	Assignee:	Tom Lane <tgl>
Status:	CLOSED NOTABUG	QA Contact:	David Lawrence <dkl>
Severity:	medium	Docs Contact:
Priority:	medium
Version:	4	CC:	hhorak
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2005-12-23 21:48:51 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Tomi Malkki 2005-12-23 18:36:03 UTC

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;

Comment 1 Tom Lane 2005-12-23 21:48:51 UTC

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.