Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Unanswered: 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
    Thank you for your attention

  2. #2
    Join Date
    Dec 2012
    Posts
    43
    The literal
    Code:
    '1 month'
    has to be quoted as literal.
    Because your SQL-code itself is already a literal you have to write '' (two times of ') for a normal ' - sign.

    An example:
    Code:
    postgres=# select 'HELLO ''world''';
       ?column?
    ---------------
     HELLO 'world'
    (1 Zeile)
    In your code:
    Code:
    RIGHT JOIN generate_series('||'2013-01-01'||'::DATE, '||'2013-12-01'||'::DATE, '||'''1 month'''||') gs(mes) ON gs.mes = e.mes';

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •