8.1 OUT params returning more than one row question
Hi,
I have been playing around with 8.1(it's very nice by the way) and was
trying to get OUT params to return more than 1 row.
I came up with the function below, and it does work, however I had to
declare another record to use in the FOR ..IN loop.
From my reading of the docs the out params create a record type
automaticly and my question is how can I use this automaticly created
record in the
FOR loop? It does not seem right that I have to create another record
type and then copy the row values to the out parms.
CREATE OR REPLACE FUNCTION test_func9(out firstname varchar, out
lastname varchar)
RETURNS SETOF record AS
$BODY$
Declare
row record;
BEGIN
for row in select null,description from common.common_groups
loop
firstname = row.description;
lastname = '';
RETURN NEXT;
end loop;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks,
Tony
On Wed, Aug 31, 2005 at 11:27:39AM -0500, Tony Caduto wrote:
Hi,
I came up with the function below, and it does work, however I had to
declare another record to use in the FOR ..IN loop.From my reading of the docs the out params create a record type
automaticly and my question is how can I use this automaticly created
record in the FOR loop? It does not seem right that I have to create
another record type and then copy the row values to the out parms.
The record that you are not supposed to declare is the output record,
i.e. you don't have to use CREATE TYPE, use a table type, or describe
the anonymous record in the SELECT statement. Of course, in PL/pgSQL
you need a variable to do the FOR ... LOOP.
Your example works fine for me. What exactly were you expecting?
--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"Escucha y olvidar�s; ve y recordar�s; haz y entender�s" (Confucio)