title: Migrating a PostgreSQL stored proc to MYSQL
body: <p>I have the following PostgreSQL which I want to re-write to make it compatible for MySQL stored function.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION test.yearly_demand_function(IN paramstartdate date, 
                                                         OUT network character varying, 
                                                         OUT season_number integer,
                                                         OUT week_trans numeric,
                                                         OUT month_trans numeric, 
                                                         OUT year_trans numeric )
    RETURNS SETOF record AS
  $BODY$
  
   BEGIN
      RETURN QUERY
          (select qq.network::varchar,
          qq.season_number,
          qq.week_trans::numeric,
          qq.month_trans::numeric,
          qq.year_trans::numeric
          from 
              ( 
              SELECT coalesce(nullif(mpf.studio,''),fi.name) AS network,
              coalesce(mpf.season_number, mpf.reported_season_number) AS season_number ,
              sum(case when activity_date &gt;= date_trunc('week', paramStartDate::timestamp) - interval '7 day' and activity_date &lt;= paramStartDate then mpf.units_sold else 0 END) as week_trans, 
              sum(case when activity_date &gt;= date_trunc('month', paramStartDate::timestamp) and activity_date &lt;= paramStartDate then mpf.units_sold else 0 END) as month_trans,
              sum(case when activity_date &gt;= date_trunc('year', paramStartDate::timestamp) and activity_date &lt;= paramStartDate then mpf.units_sold else 0 END) as year_trans
              FROM customer.dim_product_view mpf 
              left join customer.feed_indicator fi on mpf.series_name = fi.indicator_value
              and mpf.series_name = fi.indicator_value
              left join 
              (
              select p.series_name,p.season_number,count(*) as episode_count
              from product p
              where source in ('Amway','FifthThird')
              and p.episode_number is not null
              group by 1,2) as pec on mpf.series_name = pec.series_name
              and mpf.season_number = pec.season_number
              WHERE mpf.activity_date BETWEEN date_trunc('year', paramStartDate::timestamp) AND paramStartDate
              AND 1=1 
              AND ( mpf.demographic is null or '' not in ( '' ) or ('' in ( '' ) and mpf.demographic = 'Persons') ) 
              AND mpf.customer_product_id not ilike '%unallocated%'
              GROUP BY 1,2,3,7,34,35,36
              )qq
          );
  
  end;
  $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100
    ROWS 1000;
  ALTER FUNCTION yearly_demand_function(date);
  </code></pre>
  
  <p>Now Im not sure how to write the <code>RETURNS SETOF record AS</code>  from above PostgreSQL (<code>RETURN QUERY part</code>) for MYSQL</p>
  
  <p>Although I have started writing the stored proc for MY SQL as below</p>
  
  <pre><code>-- DROP FUNCTION IF EXISTS looptest;
  DELIMITER $$
  
  CREATE FUNCTION test.yearly_demand_function(    IN paramstartdate date,
                                                  OUT network varchar(256), 
                                                  OUT season_number integer,
                                                  OUT week_value numeric,
                                                  OUT month_value numeric, 
                                                  OUT year_value numeric
          ) RETURNS &lt;What to Write &gt;
            LANGUAGE SQL  
  BEGIN
  
    RETURN QUERY
          (select qq.network::varchar,
          qq.season_number,
          qq.week_value::numeric,
          qq.month_value::numeric,
          qq.year_value::numeric
          from 
              ( 
              SELECT coalesce(nullif(mm.studio,''),fi.name) AS network,
              coalesce(mm.season_number, mm.reported_season_number) AS season_number ,
              sum(case when final_date &gt;= date_trunc('week', paramStartDate::timestamp) - interval '7 day' and final_date &lt;= paramStartDate then mm.units_sold else 0 END) as week_value, 
              sum(case when final_date &gt;= date_trunc('month', paramStartDate::timestamp) and final_date &lt;= paramStartDate then mm.units_sold else 0 END) as month_value,
              sum(case when final_date &gt;= date_trunc('year', paramStartDate::timestamp) and final_date &lt;= paramStartDate then mm.units_sold else 0 END) as year_value
              FROM customer.product_view mm 
              left join customer.f_indicator fi on mm.series_name = fi.indicator_value
              and mm.series_name = fi.indicator_value
              left join 
              (
              select p.name,p.s_number,count(*) as episode_count
              from product p
              where source in ('Amway','FifthThird')
              and p.e_number is not null
              group by 1,2) as tt on mm.series_name = tt.series_name
              and mm.season_number = tt.season_number
              WHERE mm.final_date BETWEEN date_trunc('year', paramStartDate::timestamp) AND paramStartDate
              AND 1=1 
              AND ( mm.demographic is null or '' not in ( '' ) or ('' in ( '' ) and mm.demographic = 'Persons') ) 
              AND mm.customer_product_id not ilike '%unallocated%'
              GROUP BY 1,2,3,7,34,35,36
              )qq
          );
  
  
  END;
  $$
  DELIMITER 
  </code></pre>
  
  <p>how to write the <code>RETURNS SETOF record AS</code>  from above PostgreSQL (RETURN QUERY part) for MYSQL</p>