RE: [pgsql-es-ayuda] Función SQL/PL en versión 7.4

Gracias por tu respuesta Álvaro.

Por ejemplo, quiere realizar un procedimiento con esta consulta SQL que te
adjunto en el email:

SELECT forma_pago.descripcion as forma_pago,clientes.nombre, clientes.cif,
('INT'::character varying::text) || contratos.clave::text AS contrato,

sum(round("numeric"(detalle_contratos.cantidad *
(detalle_contratos.precio_unidad - detalle_contratos.precio_unidad *
detalle_contratos.porc_descuento / 100::double precision)), 2)) AS
inporte_sin_iva

FROM contratos, detalle_contratos, productos, clientes, forma_pago,
tipo_cobro

WHERE contratos.clave = detalle_contratos.c_contrato

AND contratos.b_baja = false

AND contratos.b_cerrado = true

AND contratos.b_baja_solicitada = false

AND contratos.c_cliente = clientes.clave

AND contratos.c_forma_pago = forma_pago.clave

AND detalle_contratos.b_baja = false

AND productos.clave = detalle_contratos.producto

AND productos.gratis = false

AND productos.tipo_cobro = tipo_cobro.clave

AND ((detalle_contratos.fecha_primera_factura IS NOT NULL) AND
(detalle_contratos.fecha_primera_factura >= '01/6/2011' AND
detalle_contratos.fecha_primera_factura < '01/06/2011') OR
(detalle_contratos.fecha_primera_factura < '01/5/2011' AND
productos.tipo_cobro = 2) OR (detalle_contratos.fecha_primera_factura <
'01/6/2011' AND productos.tipo_cobro = 3 AND
date_part('month',detalle_contratos.fecha_primera_factura)='5'))

GROUP BY forma_pago.clave,
forma_pago.descripcion,clientes.nombre,clientes.cif,contratos.clave

ORDER BY forma_pago.clave, forma_pago.descripcion,clientes.nombre

El procedimiento debe devolver todos los registros.

¿Cómo se debe construir la función SQL/PL?

Para ir probando (es un ejemplo, este procedimiento es incompleto y poco
funcional) he creado esta función SQL/PL:

CREATE FUNCTION test(text, text, text, integer, integer) RETURNS text AS '

DECLARE

-- Declare aliases.

primeroMes ALIAS FOR $1 -- 01/05/2011

finalMes ALIAS FOR $2 -- 01/06/2011

mes ALIAS FOR $3 -- 5

cobroMensual ALIAS FOR $4 -- 2

cobroAnual ALIAS FOR $5 -- 3

-- Declare variables to hold the customer name.

f_forma_pago text;

f_cliente text;

f_cif text;

f_contrato text;

f_importe float;

BEGIN

SELECT SELECT INTO f_forma_pago, f_cliente, f_cif, f_contrato, f_importe

forma_pago.descripcion as forma_pago,clientes.nombre, clientes.cif,
(''INT''::character varying::text) || contratos.clave::text AS contrato,

sum(round("numeric"(detalle_contratos.cantidad *
(detalle_contratos.precio_unidad - detalle_contratos.precio_unidad *
detalle_contratos.porc_descuento / 100::double precision)), 2)) AS
inporte_sin_iva

FROM contratos, detalle_contratos, productos, clientes, forma_pago,
tipo_cobro

WHERE contratos.clave = detalle_contratos.c_contrato

AND contratos.b_baja = false

AND contratos.b_cerrado = true

AND contratos.b_baja_solicitada = false

AND contratos.c_cliente = clientes.clave

AND contratos.c_forma_pago = forma_pago.clave

AND detalle_contratos.b_baja = false

AND productos.clave = detalle_contratos.producto

AND productos.gratis = false

AND productos.tipo_cobro = tipo_cobro.clave

AND ((detalle_contratos.fecha_primera_factura IS NOT NULL) AND
(detalle_contratos.fecha_primera_factura >= finalMes AND
detalle_contratos.fecha_primera_factura < finalMes) OR
(detalle_contratos.fecha_primera_factura < primeroMes AND
productos.tipo_cobro = cobroMensual) OR
(detalle_contratos.fecha_primera_factura < finalMes AND productos.tipo_cobro
= cobroAnual AND
date_part(''month'',detalle_contratos.fecha_primera_factura)= mes))

GROUP BY forma_pago.clave,
forma_pago.descripcion,clientes.nombre,clientes.cif,contratos.clave

ORDER BY forma_pago.descripcion,clientes.nombre

-- Return the name.

RETURN f_forma_pago || '':'' || f_cliente || '':'' || f_cif || '':'' ||
f_contrato || '':'' || f_importe;

END

' LANGUAGE 'plpgsql';

Errores no solucionados:

ERROR: syntax error en o cerca de "finalMes"
CONTEXT: compile of PL/pgSQL function "test" near line 6

select test('01/05/2011', '01/06/2011', '5', 2, 3)

En el caso que funcione todo correctamente, como deficiencia de la función
solo retorna el último registro…..es la primera que construyo, todo será
cuestión de ir mejorándola….

Desde ya, Gracias por todo.

Saludos.

-----Mensaje original-----
De: pgsql-es-ayuda-owner(at)postgresql(dot)org
[mailto:pgsql-es-ayuda-owner(at)postgresql(dot)org] En nombre de Alvaro Herrera
Enviado el: martes, 12 de abril de 2011 2:48
Para: Sergio Villalba Moreno
CC: pgsql-es-ayuda
Asunto: Re: [pgsql-es-ayuda] Función SQL/PL en versión 7.4

Excerpts from Sergio Villalba Moreno's message of mar abr 05 13:28:30 -0300
2011:

> Estoy intentando realizar un procedimiento almacenado con una consulta
algo

> compleja…

>

>

>

> La consulta es esta:

>

> SELECT sum(round("numeric"(float8(detalle_facturas.cantidad) *

> (detalle_facturas.precio_unidad - detalle_facturas.precio_unidad *

> detalle_facturas.porc_descuento / 100::double precision) *

> (detalle_facturas.porc_comision / 100::double precision)), 2)) AS total
FROM

> facturas, contratos, detalle_facturas, agentes WHERE facturas.c_contrato =

> contratos.clave AND facturas.clave = detalle_facturas.c_factura AND

> contratos.c_agente = agentes.clave AND contratos.b_baja = false AND

> contratos.clave=1928

Honestamente no veo en qué sentido esta consulta es compleja ni por qué

se dificultaría la creación de un procedimiento almacenado con ella.

Lo único que veo complejo es la posible pérdida de precisión y error

aritmético introducido por el uso de aritmética de punto flotante. ¿No

sería más sano convertir todo a numeric desde el principio? Es más,

¿por qué las columnas no son numeric?

> Como no consigo realizar la consulta en el procedimiento almacenado, he

> pensado en esta alternativa, aunque no funciona correctamente.

¿Podrías por favor explicar qué problema tuviste creando el

procedimiento almacenado? ¿Arrojó algún mensaje de error? Si es así,

¿cuál?

--

Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>

-

Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)

Para cambiar tu suscripcin:

http://www.postgresql.org/mailpref/pgsql-es-ayuda