viernes, 27 de junio de 2008

return query, lo nuevo de postgres 8.3.3

Hasta antes de Postgres 8.3.3, la forma en que una función retornaba un select era así (prueba es una tabla de 3 columnas y 10 filas):
create or replace function f1(out col1 varchar,out col2 varchar,out col3 varchar) returns setof record as
$$
declare
l record;
q varchar;
begin
q := $S$ select * from prueba$S$;
for l in execute q loop;
    col1 := l.col1;
    col2 := l.col2;
    col3 := l.col3;
    return next;
end loop;
return;
end;
$$
language 'plpgsql';
Ahora, la cosa se hace simple gracias al return query:
create or replace function f1()
returns setof prueba as
$$
begin
return query select * from prueba;
end;
$$
language 'plpgsql'; 

5 comentarios:

Felipe dijo...

lo malo de ese metodo es que (AFAIK) no puedes retornar un subconjunto de columnas de prueba, por lo menos no sin definir un tipo

Gotencool dijo...

Se puedes, indicando el tipo de las columnas de salida y en vez de retornar el tipo tabla retornas record:

create or replace function
f1(out var1 varchar, out var2 boolean)
returns setof record as
$$
begin
return query select var1,var2 from prueba;
end;
$$language 'plpgsql';

Lizardo dijo...

Hola, gotencool, es excelente lo del return query soluciones las consultas complejas que tenia que hacer gracias por el ejemplo, en fin posteo aca mi funcion que lo hice en EMS 2007 lite. ahh esto de postgresql siempre se debe trabajar con la herramienta que soporta la version sino es un dolor de cabeza, ademas en las consultas tuve agregarle el alias si eso no funciona, te devuelve las filas en null, aca mi jefe dice q no haga toda la logica del proyecto en funciones sino las partes basicas en fin, aca esta el codigo:
CREATE OR REPLACE FUNCTION "public"."proc_prueba2" (out cod_persona integer, out apell_paterno varchar, out apell_materno varchar, out primer_nombre varchar, out otros_nombres varchar, out num_entrehermanos integer, out num_hermanos integer) RETURNS SETOF record AS
$body$
/* New function body */
begin
return query
select p.cod_persona, p.apell_paterno, p.apell_materno, p.primer_nombre,
p.otros_nombres, a.num_entrehermanos, a.num_hermanos
from p_natural p
inner join alumno a on p.cod_persona = a.cod_persona
where p.estado_actividad = true;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

mas bien gotecool no me sale las columnas concatenadas como seria
gracias

MaGo dijo...

Intente hacerlo como dices, sin embargo todos los resultados me salian en NULL. Logŕe hacerlo de esta manera:
1) Crear un tipo personalizado con la estructura que voy a devolver:

create type holder as (departmentid int, totalsalary int8);

2) Crear la funcion que retorne un SETOF de mi tipo personalizado:

create or replace function PLpgSQLDepartmentSalaries() returns setof holder as
'
declare
r holder%rowtype;
begin
for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop
return next r;
end loop;
return;
end
'
language 'plpgsql';

Esta solución la encontré en esta página:

http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

Saludos.

Jose Luisito Salinas Ruiz dijo...

pero que pasa cuando solo quiero retorna una columna de una tabla por ejemplo si hago

CREATE OR REPLACE FUNCTION escuela.seek_alumnos(IN _appt character varying, OUT nomcompleto text, OUT _curp character varying)
RETURNS SETOF record AS
$BODY$
begin
return query select nombreCompleto,id_alum from escuela.datosAlumnos where escuela.datosAlumnos.nombreCompleto like ($1||''||'%');
END;
$BODY$
LANGUAGE plpgsql VOLATILE

funciona sin problemas pero si hago

CREATE OR REPLACE FUNCTION escuela.seek_alumnos(IN _appt character varying, OUT nomcompleto text)
RETURNS SETOF record AS
$BODY$
begin
return query select nombreCompleto from escuela.datosAlumnos where escuela.datosAlumnos.nombreCompleto like ($1||''||'%');
END;
$BODY$

LANGUAGE plpgsql VOLATILE

me manda un error que el tipo de dato debe ser text