current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

I have written a PostgreSQL function which is being called from Jasper iReport for dashboards. When I try and execute the same function manually in pgAdmin I get an error for invalid syntax for type numeric. Please find my Function which is mentioned below:

CREATE OR REPLACE FUNCTION revenue_dm.trendQuery( IN monthin integer, IN yearin integer  )
  RETURNS TABLE(month1 numeric,year1 numeric,month2 numeric,year2 numeric,month3   numeric,year3 numeric ) AS
$BODY$
       DECLARE

        BEGIN

           month1='select extract (month from (select concat(monthin,''-'',15,''-'',yearin)::date)-30)::numeric';
           month2='select extract (month from (select concat(monthin,''-'',15,''-'',yearin)::date)-60)::numeric';
           month3='select extract (month from (select concat(monthin,''-'',15,''-'',yearin)::date)-90)::numeric';
           year1= 'select extract (year from (select concat(monthin,''-'',15,''-'',yearin)::date)-30)::numeric';
           year2= 'select extract (year from (select concat(monthin,''-'',15,''-'',yearin)::date)-60)::numeric';
           year3= 'select extract (year from (select concat(monthin,''-'',15,''-'',yearin)::date)-90)::numeric';

          RETURN QUERY EXECUTE month1;
          RETURN QUERY EXECUTE month2;
          RETURN QUERY EXECUTE month3;
          RETURN QUERY EXECUTE year1;
          RETURN QUERY EXECUTE year2;
          RETURN QUERY EXECUTE year3;
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

I am using the following query to execute my function in pgAdmin.

 SELECT revenue_dm.trendQuery(04,2014);

After executing the function, this is the error that I am getting:

ERROR:  invalid input syntax for type numeric: "select extract (month from (select concat(monthin,'-',15,'-',yearin)::date)-30)::numeric"
CONTEXT:  PL/pgSQL function revenue_dm.trendquery(integer,integer) line 6 at assignment

I am not able to debug where in my code am I going wrong in using the dynamic SQL in this function.

Can anyone please help me out in finding out where am I going wrong in this particular function.

share|improve this question

1 Answer 1

up vote 2 down vote accepted
  1. You cannot use outer variables in dynamic sql. Use format() or some similar escape mechanism; or use the USING clause for EXECUTE (preferred)
  2. RETURNS TABLE(month1 numeric ... is equal to define an OUT parameter, which is month1 numeric. In that point of view, you cannot assign a query string to that variable, Postgres will fail trying to parse it as numeric.

Just run a single 

RETURN QUERY EXECUTE 'SELECT <multiple fields> ...' USING monthin, yearin;

Edit: just realized, you shouldn't do a dynamic query at all (there no need for them here)

Just run a single

RETURN QUERY SELECT <multiple fields> ...;
share|improve this answer
    
How can I assign the result of my single statement RETURN QUERY SELECT <multiple fields> ...; to my OUT variable? –  Yousuf Sultan Apr 30 '14 at 9:50
1  
No need to assign anything. RETURN QUERY already returns the values its selected. –  pozs Apr 30 '14 at 9:56
    
In My case I have to get three months in three different parameters... So what you are saying, is that if i give RETURN QUERY select extract (month from (select concat(monthin,'-',15,'-',yearin)::date)-30)::numeric; then it will return it to the first parameter... and so on respectively??? Is it like that? –  Yousuf Sultan Apr 30 '14 at 11:29
1  
No. Return all column in one query. Like RETURN QUERY SELECT 1, 2, 3, 4, 5, 6 –  pozs Apr 30 '14 at 12:48
    
Thanks pzs... it worked out.. –  Yousuf Sultan May 1 '14 at 19:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.