FUNCIONES

From: SERGIO <screspo(at)rollerstar(dot)com>
To: pgsql-es-ayuda(at)postgresql(dot)org
Subject: FUNCIONES
Date: 2008-02-08 11:39:34
Message-ID: 200802081239.34408.screspo@rollerstar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda


Hola lista .

Tengo la siguiente funcion:
CREATE TABLE p_n1
(
n1 character varying(10) NOT NULL,
mes integer NOT NULL,
suma_debe numeric,
suma_haber numeric,
CONSTRAINT pn1_pk PRIMARY KEY (n1, mes)
)
WITHOUT OIDS;
###
CREATE OR REPLACE FUNCTION nivel_1(bpchar, bpchar)
RETURNS SETOF p_n1 AS
$BODY$
select n1::varchar,mes::int,sum(debe)::numeric as
suma_debe,sum(haber)::numeric as suma_haber from plan_mes p
where p.empresa= $1 and p.n1= $2 group by 1,2;
$BODY$
LANGUAGE 'sql' VOLATILE;

FUNCIONA PERFECTAMENTE
cuando ejecuto
select * from nivel_1('120071','4');
me devuelve:
n1 | mes | suma_debe | suma_haber
----+-----+------------+------------
4 | 1 | 3106226.29 | 3424375.15
4 | 2 | 3593066.20 | 3324942.78
4 | 3 | 3710847.38 | 3343387.05
4 | 4 | 3140850.28 | 3033357.66
4 | 5 | 3564528.54 | 3552877.26
4 | 6 | 3679274.45 | 3716740.07
4 | 7 | 3862747.63 | 3618133.31
4 | 8 | 642645.30 | 1184882.23
4 | 9 | 3659732.72 | 3721305.76
4 | 10 | 3591430.14 | 3876894.66
4 | 11 | 2889243.02 | 1688738.58
4 | 12 | 125115.30 | 482823.17
(12 filas)

select nivel_1('120071','4') ;
me devuelve:

"(4,2,3593066.20,3324942.78)"
"(4,3,3710847.38,3343387.05)"
"(4,1,3106226.29,3424375.15)"
"(4,6,3679274.45,3716740.07)"
"(4,7,3862747.63,3618133.31)"
"(4,4,3140850.28,3033357.66)"
"(4,5,3564528.54,3552877.26)"
"(4,10,3591430.14,3876894.66)"
"(4,11,2889243.02,1688738.58)"
"(4,8,642645.30,1184882.23)"
"(4,9,3659732.72,3721305.76)"
"(4,12,125115.30,482823.17)"

PERO NO SE COMO HACER JOIN CON OTRAS TABLAS:
POR EJEMPLO:
SELECT p.empresa,p.codigo,p.nombre,nivel_1(p.empresa,p.codigo) FROM plan p
WHERE p.empresa='120071' AND p.codigo='4' ;
DEVUELVE
empresa | codigo | nombre |
nivel_1
---------+------------+------------------------------------------+------------------------------
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,2,3593066.20,3324942.78)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,3,3710847.38,3343387.05)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,0,3108585.67,1077530.77)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,1,3106226.29,3424375.15)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,6,3679274.45,3716740.07)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,7,3862747.63,3618133.31)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,4,3140850.28,3033357.66)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,5,3564528.54,3552877.26)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,13,0.00,0.00)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,10,3591430.14,3876894.66)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,11,2889243.02,1688738.58)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,8,642645.30,1184882.23)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,9,3659732.72,3721305.76)
120071 | 4 | ACREED.Y DEUD.P/OP.TRAF. |
(4,12,125115.30,482823.17)
(14 filas)

HAY ALGUNA MANERA DE QUE DEVUELVA LAS COLUMNAS DE LA FUNCION COMO LOS DEMAS
CAMPOS DE LA SELECT ?
GRACIAS

Responses

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message informes 2008-02-08 11:40:31 RE: Ayuda
Previous Message Jaime Casanova 2008-02-08 05:51:09 Re: Ayuda Tunning Postgres 8.2.6