#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep 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
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    there is no
    Code:
    1 month
    in PostgreSQL. I guess you meant
    Code:
    interval '1 month'
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep 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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    It's not interval 1 month

    It's either:
    Code:
    interval '1 month'
    or
    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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep 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.

IMN logo majestic logo threadwatch logo seochat tools logo