Bug 176497 - PostgreSQL plpgsql function bug
PostgreSQL plpgsql function bug
Status: CLOSED NOTABUG
Product: Fedora
Classification: Fedora
Component: postgresql (Show other bugs)
4
All Linux
medium Severity medium
: ---
: ---
Assigned To: Tom Lane
David Lawrence
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2005-12-23 13:36 EST by Tomi Malkki
Modified: 2013-07-02 23:07 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2005-12-23 16:48:51 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Tomi Malkki 2005-12-23 13:36:03 EST
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 16:48:51 EST
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.

Note You need to log in before you can comment on or make changes to this bug.