asked on

plpgsql function - not able to use FOUND

The following plpgsql function declaration works:
--------8<--------
/*
* Verified 2006-09-02 working for UPDATE and INSERT
* Was not working when the FOUND variable was tested
*/
DECLARE
      auctionId ALIAS FOR $1;
      organizationId ALIAS FOR $2;
      positionNumber ALIAS FOR $3;
      unixTimeStamp ALIAS FOR $4;
      currentOrderId int4;
      retVal varchar;
BEGIN
      SELECT INTO currentOrderId "AuctionOrganizationId" FROM "auction_organization" WHERE "AuctionId"=auctionId AND "OrganizationId"=organizationId;
      If currentOrderId IS NOT NULL Then
            UPDATE "auction_organization" SET "Position"=positionNumber, "LastModified"=unixTimeStamp WHERE "AuctionOrganizationId"=currentOrderId;
            RETURN currentOrderId;
      Else
            INSERT INTO "auction_organization" ("AuctionId", "OrganizationId", "Position", "DateAdded", "LastModified") VALUES (auctionId, organizationId, positionNumber, unixTimeStamp, unixTimeStamp);
            RETURN 'currentOrderId does not exist';
      End If;
END;
--------8<--------

I had to fix it because the original IF statement had been...

If (currentOrderId) Then

... in a PostgreSQL 7.0.3 application, and Ifs apparently require booleans now in 8.1.4.

I then changes this to what I expected to be the right thing:

      If FOUND Then

...but FOUND didn't work for me.

Shouldn't I have been able to use FOUND rather than having to test for NULL??????

ASKER CERTIFIED SOLUTION

ravenpl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rstaveley

ASKER

Dammit... it is working now. Must be gremlins.

rstaveley

ASKER

It had been returning no error message and returning the presumably NULL currentOrderId presumably having failed to UPDATE.

i.e. I saw this with psql with IF FOUND THEN, function name being "ordersingleconsignor":
--------8<--------
mydb=# delete from auction_organization where "AuctionId" = 50;
DELETE 1
mydb=# select ordersingleconsignor(50,1318,1,'1157201972');
ordersingleconsignor
----------------------
(1 row)
--------8<--------

However the IF FOUND THEN condition now behaves OK!?!

i.e.
--------8<--------
mydb=# delete from auction_organization where "AuctionId" = 50;
DELETE 1
mydb=# select ordersingleconsignor(50,1318,1,'1157201972');
ordersingleconsignor
-------------------------------
currentOrderId does not exist
(1 row)

morrell=# select ordersingleconsignor(50,1318,1,'1157201972');
ordersingleconsignor
----------------------
1500
(1 row)
--------8<--------

I hope I was mistaken and this isn't an intermittent error. In any case, your answer was right, ravenpl. Many thanks.