Cursors and For Loop's on PGSQL

I've got this nifty ported function. I have no data yet so I'm not exactly unit testing yet. But my question is around opening and closing cursors. Does a FOR loop imply a cursor being opened or do I have to explicitly open it?

\nCREATE OR REPLACE FUNCTION getDependentsForPlan(integer, integer, integer) RETURNS text AS\n'\n    DECLARE\n        erId alias for $1;\n        eeId alias for $2;\n        pId alias for $3;\n        text dependents;\n        CURSOR c1 FOR\n                    SELECT DependentSSN, RowNum depId\n                    FROM EmployeePeDependentsCoverage\n                    WHERE EmployerId = erId AND EmployeeSSN = eeId AND PlanId = pId\n                    ORDER BY DependentSSN;\n   BEGIN\n            dependents := \\'\\';\n            OPEN c1; <------------------------------------------------------Do I need this?\n            FOR emp_rec IN c1 LOOP\n                dependents :=  dependents || \\', \\' || emp_rec.depId;\n            END LOOP;\n            CLOASE c1; <-----------------------------------------------------and this?\n            return SUBSTR(trim(dependents), 3);\n   END'\n   language 'plpgsql';\n  \n

Post #139,401 by tuberculosis 2/2/04 7:10:20 PM Reply	Cursors and For Loop's on PGSQL I've got this nifty ported function. I have no data yet so I'm not exactly unit testing yet. But my question is around opening and closing cursors. Does a FOR loop imply a cursor being opened or do I have to explicitly open it? \nCREATE OR REPLACE FUNCTION getDependentsForPlan(integer, integer, integer) RETURNS text AS\n'\n DECLARE\n erId alias for $1;\n eeId alias for $2;\n pId alias for $3;\n text dependents;\n CURSOR c1 FOR\n SELECT DependentSSN, RowNum depId\n FROM EmployeePeDependentsCoverage\n WHERE EmployerId = erId AND EmployeeSSN = eeId AND PlanId = pId\n ORDER BY DependentSSN;\n BEGIN\n dependents := \\'\\';\n OPEN c1; <------------------------------------------------------Do I need this?\n FOR emp_rec IN c1 LOOP\n dependents := dependents \|\| \\', \\' \|\| emp_rec.depId;\n END LOOP;\n CLOASE c1; <-----------------------------------------------------and this?\n return SUBSTR(trim(dependents), 3);\n END'\n language 'plpgsql';\n \n .....To the future or to the past, to a time when thought is free, when men are different from one another and do not live alone -- to a time when truth exists and what is done cannot be undone: .....From the age of uniformity, from the age of solitude, from the age of Big Brother, from the age of doublethink -- greetings! -- George Orwell, 1984
Post #139,417 by admin 2/2/04 7:35:29 PM Reply	Does that work? Can you use FOR with a cursor? I can only find references to using FOR with a select query. If it works, you shouldn't have to open/close the cursor. Also, it's "CLOSE", not "CLOASE"... ;-) Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #139,670 by tuberculosis 2/3/04 11:00:15 PM Reply	Apparently not :-( Functions are turning into a drag as the thing isn't checked until invoked. Kind of a bummer. I've ended up tossing cursors and just doing for loops on the query. .....To the future or to the past, to a time when thought is free, when men are different from one another and do not live alone -- to a time when truth exists and what is done cannot be undone: .....From the age of uniformity, from the age of solitude, from the age of Big Brother, from the age of doublethink -- greetings! -- George Orwell, 1984

Welcome to IWETHEY!