Error in PL/pgSQL function

Dev Shed Forums Sponsor:

January 26th, 2013, 10:51 AM

alphak01

Registered User

Join Date: Jan 2013

Posts: 8

Time spent in forums: 2 h 11 m 41 sec
Reputation Power: 0

Error in PL/pgSQL function

Hello, I have an error in the function below,
I am not able to solve,
should probably be in quotes

Code:

CREATE OR REPLACE FUNCTION formula_cont_nv2(mesi integer, mesf integer, anoi integer, anof integer)
  RETURNS text AS
$BODY$
DECLARE
sqlbusca text := '';
contabilizacoes record;

BEGIN
sqlbusca :=  'WITH v_entradas
			AS (
				SELECT date_trunc(month, ent_data) mes
					,sum(ent_valor) soma
				FROM entradas
				WHERE extract(year FROM ent_data) = extract(year FROM now())
				GROUP BY 1
				)
				,v_saidas
			AS (
				SELECT date_trunc(month, sai_data) mes
					,sum(sai_valor) soma
				FROM saidas
				WHERE extract(year FROM sai_data) = extract(year FROM now())
				GROUP BY 1
				)
			SELECT extract(year FROM gs.mes)::NUMERIC ano
					,extract(month FROM gs.mes)::NUMERIC mes
					,round(coalesce(e.soma, 0), 2) entrada
					,round(coalesce(s.soma, 0), 2) saida
					,round(coalesce(e.soma, 0) - coalesce(s.soma, 0), 2) saldo
					,round(coalesce((100 * s.soma) / e.soma, 0), 2) percentual
			FROM v_entradas e
			INNER JOIN v_saidas s ON e.mes = s.mes
			RIGHT JOIN generate_series('||'2013-01-01'||'::DATE, '||'2013-12-01'||'::DATE, '||'1 month'||') gs(mes) ON gs.mes = e.mes';
      for contabilizacoes in EXECUTE(sqlbusca)loop
	EXECUTE 'DELETE FROM CONTABILIZACOES';
	
	EXECUTE 'INSERT INTO contabilizacoes (con_mes,con_ano) values ('||contabilizacoes.mes||','||contabilizacoes.ano||')';
      end loop;


	RETURN 'OK';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Code:

select formula_cont_nv2(1,2,2013,2013)

Code:

ERROR: syntax error at or near "month"
LINE 25: ...rate_series(2013-01-01::DATE, 2013-12-01::DATE, 1 month) gs(...
                                                              ^
QUERY:  WITH v_entradas

January 26th, 2013, 12:30 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,877

Time spent in forums: 1 Month 2 Weeks 2 Days 5 h 32 m
Reputation Power: 813

Hi,

there is no

Code:

1 month

in PostgreSQL. I guess you meant

Code:

interval '1 month'

January 26th, 2013, 04:26 PM

alphak01

Registered User

Join Date: Jan 2013

Posts: 8

Time spent in forums: 2 h 11 m 41 sec
Reputation Power: 0

Hi
I added the following line:

Code:

interval '1 month'

But still giving error:

Code:

ERROR: syntax error at or near "1"
LINE 25: ...ries (2013-1-01::date,2013-12-01::date + interval 1 month) g...

Function with changes:

Code:

CREATE OR REPLACE FUNCTION formula_cont_nv2(mesi integer, mesf integer, anoi integer, anof integer)
  RETURNS text AS
$BODY$
DECLARE
sqlbusca text := '';
contabilizacoes record;

BEGIN
sqlbusca := 'WITH v_entradas
			AS (
				SELECT date_trunc(month, ent_data) mes
					,sum(ent_valor) soma
				FROM entradas
				WHERE extract(year FROM ent_data) = extract(year FROM now())
				GROUP BY 1
				)
				,v_saidas
			AS (
				SELECT date_trunc(month, sai_data) mes
				,sum(sai_valor) soma
				FROM saidas
				WHERE extract(year FROM sai_data) = extract(year FROM now())
				GROUP BY 1
				)
			SELECT extract(year FROM gs.mes)::NUMERIC ano
					,extract(month FROM gs.mes)::NUMERIC mes
					,round(coalesce(e.soma, 0), 2) entrada
					,round(coalesce(s.soma, 0), 2) saida
					,round(coalesce(e.soma, 0) - coalesce(s.soma, 0), 2) saldo
					,round(coalesce((100 * s.soma) / e.soma, 0), 2) percentual
			FROM v_entradas e
			INNER JOIN v_saidas s ON e.mes = s.mes
			RIGHT OUTER JOIN generate_series ('||'2013-1-01'||'::date,'||'2013-12-01'||'::date + interval '||'1'||' month'||') gs(mes) on gs.mes = e.mes';
      for contabilizacoes in EXECUTE(sqlbusca)loop
	EXECUTE 'DELETE FROM CONTABILIZACOES';
	
	EXECUTE 'INSERT INTO contabilizacoes (con_mes,con_ano) values ('||contabilizacoes.mes||','||contabilizacoes.ano||')';
      end loop;


	RETURN 'OK';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Thanks For Attention

January 26th, 2013, 05:04 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 57 m 3 sec
Reputation Power: 284

It's not interval 1 month

It's either:

Code:

interval '1 month'

Code:

interval '1' month

Note the single quotes.

__________________
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code

Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html

January 26th, 2013, 05:53 PM

alphak01

Registered User

Join Date: Jan 2013

Posts: 8

Time spent in forums: 2 h 11 m 41 sec
Reputation Power: 0

My problem is this line:

Code:

RIGHT OUTER JOIN generate_series ('||'2013-1-01'||'::date,'||'2013-12-01'||'::date + interval '||'1'||' month'||') gs(mes) on gs.mes = e.mes';

I am not able to solve the error of single quotes.

Error in PL/pgSQL function

Developer Shed Advertisers and Affiliates