Re: Procedure en postgres

2006/11/5, Eliana Gutierrez <egp1962(at)yahoo(dot)com(dot)au>:
>
> Estoy usando este procedure
>
> CREATE FUNCTION myfun(OUT mycursor refcursor) AS
> $BODY$begin
> OPEN mycursor FOR
> SELECT
> processes."PT_DLL_ID" AS pt_dll_id,
> processes."PT_P_ID" AS pt_p_id,
> processes."PT_CONTEXT" AS pt_context,
> processes."PT_CURRENT_STEP" AS pt_current_step,
> processes."PT_TIMER_IN" AS pt_timer_in,
> processes."PT_TIMER_OUT" AS pt_timer_out,
> processes."PT_PRIORITY" AS pt_priority,
> processes."PT_STATUS" AS pt_status,
> processes."PT_CHAMELEON_ID" AS pt_chameleon,
> dllregistry."DLLT_DLL_LOCATION" AS dllt_dll_location,
> processregistry."PR_LOCATIONT_ORI" AS pr_location_ori
> FROM processes JOIN dllregistry ON processes."PT_DLL_ID" =
> dllregistry."DLLT_DLL_ID"
> JOIN processregistry ON processes."PT_P_ID" = processregistry."PR_P_ID"
> AND processes."PT_DLL_ID" = processregistry."PR_DLL_ID"
> WHERE "PT_STATUS" = 0
> ORDER BY pt_dll_id, pt_p_id, pt_context
> LIMIT 1;
> CLOSE mycursor;
> END;$BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> nose que estoy haciendo mal que al ejecutarlo por pgadmin con la sentence
> SELECT myfun(); me retorna
> Row myfun(refcursor)
> 1 "<unnamed portal 5>"
>
>
> Agracederia si alguien puede ayudarme pls.

Hola eliana, necesariamente necesitas usar cursores?, por lo que leí tu
anterior pregunta solo quieres tuplas con sus columnas, y lo podrias hacer
algo así:

CREATE OR REPLACE FUNCTION nombre_funcion() RETURNS SETOF RECORD AS '
DECLARE registro RECORD;
BEGIN
FOR registro IN _aqui_tu_consulta LOOP
RETURN NEXT registro;
END LOOP;
RETURN;
END;
'LANGUAGE 'plpgsql';

y para llamarlo simplemente:

SELECT * FROM nombre_funcion() AS (campo tipo, campo tipo, campo tipo,
....);

Saludos.

Eliana
>
> Send instant messages to your online friends http://au.messenger.yahoo.com
>

--
Linder Poclaba Lázaro.
Desarrollador Key Optimal Solutions - KEYOS.
Usa Software Libre y obtiene tú libertad.