Re: Pasar parametro string a funcion en 7.4.8

Hola amigos, tengo un problema que ya me esta partiendo la cabeza:

En mi trabajo se utiliza el server:
"PostgreSQL 7.4.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2
20050816 (prerelease) (Debian 4.0.1-5)"

Aclaro que ya he sugerido varias veces que lo actualicen pero no pasa
nada, así que es lo que tengo y me tengo que pelear con el =).

Lo que necesito es crear una funcion a partir de la siguiente consulta sql:

--Tanteo General
Select
sum(doc_importe) as importe_facturas,
round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100) as
treinta_porcent_del_imp_del_iva,
round(((round(sum(doc_importe)/1.1))*2)/100) as dos_porcent_del_imp_sin_iva,
round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100)
as cero_cinco,
(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))+(round(((round(sum(doc_importe)/1.1))*2)/100))+(round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100))
as total_retenciones
from docus_resp
where doc_id in (2,3);

Los valores que debe retornar son:
2423700;66101;44067;11568;121736

Los valores de la tabla son:
Select doc_id, doc_importe from docus_resp where doc_id in (2,3);
2;1000000
3;1423700

Estoy intentando, pero tengo un problema al pasarle los parametros,
con la siguiente funcion:

CREATE OR REPLACE FUNCTION retenciones_importes(character) RETURNS RECORD AS'
DECLARE
ids alias for $1;
resultado RECORD;

BEGIN
Select into resultado
sum(doc_importe) as importe_facturas,
round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100) as
treinta_porcent_del_imp_del_iva,
round(((round(sum(doc_importe)/1.1))*2)/100) as dos_porcent_del_imp_sin_iva,
round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100)
as cero_cinco,
(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))+(round(((round(sum(doc_importe)/1.1))*2)/100))+(round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100))
as total_retenciones
from docus_resp
where doc_id in (ids);
return resultado;
END;'
LANGUAGE plpgsql;

y la llamo de esta forma:

Select * from retenciones_importes('2,3') as (importe_facturas
numeric, treinta_porcent_del_imp_del_iva numeric,
dos_porcent_del_imp_sin_iva numeric, cero_cinco numeric,
total_retenciones numeric);

He releido varias veces el manual en la parte:
http://www.postgresql.org/docs/7.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES

Y la verdad que no entiendo tanto... (mi ingles no es tan bueno)...

En fín, alguna ayudita???

Desde ya muchas gracias y disculpen la longitud del mail....
--
§~^Calabaza^~§ from Villa Elisa, Paraguay
----------------
A hendu hína: Demolition Hammer - 04
http://foxytunes.com/artist/demolition+hammer/track/04

El 7/11/07, Calabaza Calabaza <calalinux(at)gmail(dot)com> escribió:
> Hola amigos, tengo un problema que ya me esta partiendo la cabeza:
>
> En mi trabajo se utiliza el server:
> "PostgreSQL 7.4.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2
> 20050816 (prerelease) (Debian 4.0.1-5)"
>
> Aclaro que ya he sugerido varias veces que lo actualicen pero no pasa
> nada, así que es lo que tengo y me tengo que pelear con el =).
>
> Lo que necesito es crear una funcion a partir de la siguiente consulta sql:
>
> --Tanteo General
> Select
> sum(doc_importe) as importe_facturas,
> round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100) as
> treinta_porcent_del_imp_del_iva,
> round(((round(sum(doc_importe)/1.1))*2)/100) as dos_porcent_del_imp_sin_iva,
> round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100)
> as cero_cinco,
> (round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))+(round(((round(sum(doc_importe)/1.1))*2)/100))+(round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100))
> as total_retenciones
> from docus_resp
> where doc_id in (2,3);
>
> Los valores que debe retornar son:
> 2423700;66101;44067;11568;121736
>
> Los valores de la tabla son:
> Select doc_id, doc_importe from docus_resp where doc_id in (2,3);
> 2;1000000
> 3;1423700
>
>
> Estoy intentando, pero tengo un problema al pasarle los parametros,
> con la siguiente funcion:
>
> CREATE OR REPLACE FUNCTION retenciones_importes(character) RETURNS RECORD AS'
> DECLARE
> ids alias for $1;
> resultado RECORD;
>
> BEGIN
> Select into resultado
> sum(doc_importe) as importe_facturas,
> round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100) as
> treinta_porcent_del_imp_del_iva,
> round(((round(sum(doc_importe)/1.1))*2)/100) as dos_porcent_del_imp_sin_iva,
> round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100)
> as cero_cinco,
> (round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))+(round(((round(sum(doc_importe)/1.1))*2)/100))+(round((((sum(doc_importe))-(round(((sum(doc_importe)-round(sum(doc_importe)/1.1))*30)/100))-(round(((round(sum(doc_importe)/1.1))*2)/100)))*0.5)/100))
> as total_retenciones
> from docus_resp
> where doc_id in (ids);
> return resultado;
> END;'
> LANGUAGE plpgsql;
>
> y la llamo de esta forma:
>
> Select * from retenciones_importes('2,3') as (importe_facturas
> numeric, treinta_porcent_del_imp_del_iva numeric,
> dos_porcent_del_imp_sin_iva numeric, cero_cinco numeric,
> total_retenciones numeric);
>
> He releido varias veces el manual en la parte:
> http://www.postgresql.org/docs/7.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
>
> Y la verdad que no entiendo tanto... (mi ingles no es tan bueno)...
>
> En fín, alguna ayudita???

¿ Por qué no hacer un vista ?

>
> Desde ya muchas gracias y disculpen la longitud del mail....
> --
> §~^Calabaza^~§ from Villa Elisa, Paraguay
> ----------------
> A hendu hína: Demolition Hammer - 04
> http://foxytunes.com/artist/demolition+hammer/track/04
> --
> TIP 4: No hagas 'kill -9' a postmaster
>

--
_________________________________
Solo soy una mente genial en un cuerpo

El 7/11/07, usuario anonimo <opinante(dot)anonimo(at)gmail(dot)com> escribió:
>
> ¿ Por qué no hacer un vista ?

La funcion basicamente hace calculos sobre la sumatoria de los
registros pasados como parametros,
y si no tengo estos parametros de antemano no puedo crear la vista.

Yo por lo menos no sé crear una vista parametrizada, existe alguna forma?

--
§~^Calabaza^~§ from Villa Elisa, Paraguay
----------------
A hendu hína: Demolition Hammer - 08
http://foxytunes.com/artist/demolition+hammer/track/08

El 7/11/07, Calabaza Calabaza <calalinux(at)gmail(dot)com> escribió:
> El 7/11/07, usuario anonimo <opinante(dot)anonimo(at)gmail(dot)com> escribió:
> >
> > ¿ Por qué no hacer un vista ?
>
> La funcion basicamente hace calculos sobre la sumatoria de los
> registros pasados como parametros,
> y si no tengo estos parametros de antemano no puedo crear la vista.
>
> Yo por lo menos no sé crear una vista parametrizada, existe alguna forma?

si haces un vista de la siguiente consulta:

select
doc_id,
sum(doc_importe) as importe_facturas...
from
docus_resp
group by 1;

despues puedes hacer una consulta sobre esta vista asi:

select * from vista_consulta_anterior where doc_id in (................);

por qué esto no soluciona tu problema ?

yo haría una funcion de "total_retenciones"...

>
> --
> §~^Calabaza^~§ from Villa Elisa, Paraguay
> ----------------
> A hendu hína: Demolition Hammer - 08
> http://foxytunes.com/artist/demolition+hammer/track/08
> --
> TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán
>

--
_________________________________
Solo soy una mente genial en un cuerpo

Si he entendido bien, quieres obtener cosas...
...where doc_id in (2,3)
es decir, donde el doc_id ES IGUAL AL NÚMERO 2 ó AL NÚMERO 3
¿y lo quieres hacer pasando como parámetro un TEXTO '2,3' ?????

Pues no lo estás haciendo bien, obviamente. Por eso te dará un error
al tratar de
comparar el número doc_id con el texto '2,3' (suponiendo que doc_id es
un número, ya que no das la descripción de la tabla).

Si estoy en lo cierto, puedes probar
a) SQL dinámico (construirte el select a partir de strings incluyendo
el parámetro)

b) Usar un array en el parámetro. Mira este ejemplo:

postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test values(2),(4);
INSERT 0 2
postgres=# select * from test;
id
----
2
4
(2 rows)

postgres=# select * from test where id in ('2,3'); <-----Falla porque
no son la lista del 2 y el 3
ERROR: invalid input syntax for integer: "2,3"
postgres=# select * from test where id = any ('{2,3}'::int[]); <---
Esto sí funciona
id
----
2
(1 row)

El 7/11/07, Miguel Rodríguez Penabad <penabad(at)gmail(dot)com> escribió:
> Si he entendido bien, quieres obtener cosas...
> ...where doc_id in (2,3)
> es decir, donde el doc_id ES IGUAL AL NÚMERO 2 ó AL NÚMERO 3
> ¿y lo quieres hacer pasando como parámetro un TEXTO '2,3' ?????
>
> Pues no lo estás haciendo bien, obviamente. Por eso te dará un error
> al tratar de
> comparar el número doc_id con el texto '2,3' (suponiendo que doc_id es
> un número, ya que no das la descripción de la tabla).

Sí es un integer, disculpas por no incluirlo....

> Si estoy en lo cierto, puedes probar
> a) SQL dinámico (construirte el select a partir de strings incluyendo
> el parámetro)

Esto no entiendo, como se hace?
(Acepto como respuestas links o palabras claves para mi amigo google =))

> b) Usar un array en el parámetro.

Esta opcion me gusto!

> postgres=# select * from test where id in ('2,3'); <-----Falla porque
> no son la lista del 2 y el 3
> ERROR: invalid input syntax for integer: "2,3"

Este es el error que tenía...

> postgres=# select * from test where id = any ('{2,3}'::int[]); <---
> Esto sí funciona
> id
> ----
> 2
> (1 row)

Bueno, sobre la solución tengo algunas preguntas:

He realizado lo sgte:

Modifique el inicio de la funcion de esta forma:
CREATE OR REPLACE FUNCTION retenciones_importes(int[]) RETURNS RECORD AS

Luego lo llamo de esta forma:

Select retenciones_importes('{2,3}'::int[]);

Y me devuelve el sgte error:

ERROR: cannot display a value of type record
Estado SQL:0A000

Y el manual dice para este error: FEATURE NOT SUPPORTED

Como me doy cuenta, la devolucion del tipo RECORD es lo que esta fallando,
como puedo hacer entonces para devolver el registro?

Desde ya muchas gracias por el tiempo amigos.

--
§~^Calabaza^~§ from Villa Elisa, Paraguay

El 8/11/07, Calabaza Calabaza <calalinux(at)gmail(dot)com> escribió:
> > Si estoy en lo cierto, puedes probar
> > a) SQL dinámico (construirte el select a partir de strings incluyendo
> > el parámetro)
>
> Esto no entiendo, como se hace?
> (Acepto como respuestas links o palabras claves para mi amigo google =))

Mira este ejemplo (que evidentemente es mejorable, y usa además la
sintaxis de postgres 8.2) que hace un select incluyendo una condición
que se pasa como parámetro.
Hay documentación oficial sobre los cursores en (por ejemplo)
http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html

create or replace function b(condicion text) returns record
as $$
DECLARE
c refcursor;
orden text;
registro record;
BEGIN
orden := 'SELECT 1,2,3';
open c for execute orden || ' where '|| condicion;
fetch c into registro;
close c;
return registro;
END;
$$ language plpgsql;

penabad=> select b('1=1');
b
---------
(1,2,3)
(1 row)

penabad=> select b('1=0');
b
------
(,,)
(1 row)

>
> > b) Usar un array en el parámetro.
>
> Esta opcion me gusto!

Me alegro :)
> Bueno, sobre la solución tengo algunas preguntas:
>
> He realizado lo sgte:
>
> Modifique el inicio de la funcion de esta forma:
> CREATE OR REPLACE FUNCTION retenciones_importes(int[]) RETURNS RECORD AS
>
> Luego lo llamo de esta forma:
>
> Select retenciones_importes('{2,3}'::int[]);
>
> Y me devuelve el sgte error:
>
> ERROR: cannot display a value of type record
> Estado SQL:0A000

¿Puedes poner la definición completa de la función?
En principio devolver un record no debería dar ese tipo de error,
según mi experiencia.

Saludos

--
Miguel Rodríguez Penabad

El 8/11/07, Miguel Rodríguez Penabad <penabad(at)gmail(dot)com> escribió:
> El 8/11/07, Calabaza Calabaza <calalinux(at)gmail(dot)com> escribió:
> > > a) SQL dinámico (construirte el select a partir de strings incluyendo
> > > el parámetro)

> > Esto no entiendo, como se hace?
> > (Acepto como respuestas links o palabras claves para mi amigo google =))

> Mira este ejemplo (que evidentemente es mejorable, y usa además la
> sintaxis de postgres 8.2) que hace un select incluyendo una condición
> que se pasa como parámetro.
> Hay documentación oficial sobre los cursores en (por ejemplo)
> http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html

Ok voy a mirar, gracias! =)

> > > b) Usar un array en el parámetro.

> > Bueno, sobre la solución tengo algunas preguntas:

> > Y me devuelve el sgte error:
> >
> > ERROR: cannot display a value of type record
> > Estado SQL:0A000

> ¿Puedes poner la definición completa de la función?

Ok Miguel,
la definición completa la envié en el primer mail de este hilo puedes
mirarlo aquí:

http://archives.postgresql.org/pgsql-es-ayuda/2007-11/msg00129.php

> En principio devolver un record no debería dar ese tipo de error,
> según mi experiencia.

Sí, realmente en versiones nuevas todo funciona a la perfeccion, pero
en este "vegestorio" que estamos usando aqui aparecen todos los
problemas juntos!

(Ya estoy preparando un informe para demostrar la importancia de
actualizar a las versiones mas nuevas... pero es imperativamente mas
importe terminar el software a tiempo...)

> Saludos
Gracias de nuevo, a todos y en especial a tí, por la ayuda =)

--
§~^Calabaza^~§ from Villa Elisa, Paraguay
----------------
A hendu hína: 1864 - 1949 RICHARD STRAUSS - The Song of the Grave
http://foxytunes.com/artist/1864+-+1949+richard+strauss/track/the+song+of+the+grave