title: SQL Server Stored Procedure in Postgres
body: <p>I want to write a function in Postgres for the Microsoft SQL Server stored procedure shown here.
  Please help. I want to use this stored procedure in postgres as function.</p>
  
  <pre><code>USE [EMS_Demo_Db]
  GO
  /****** Object:  StoredProcedure [dbo].[SP_HourWise]    Script Date: 16-12-2014 10:48:53 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[SP_HourWise]
  @date_param datetime 
  AS
  BEGIN
      SET NOCOUNT ON;
  DECLARE @start_time datetime = dateadd(HOUR, 6, @date_param)
  
  ; WITH Hours AS (
     SELECT dateadd(HOUR, row_number() OVER(ORDER BY From_Time) - 1, @start_time) AS From_Time,
            dateadd(HOUR, row_number() OVER(ORDER BY From_Time), @start_time) AS To_Time
     FROM   Table_2
  )
  SELECT CONVERT(varchar,@date_param, 103)
   as Date_def, convert(time(0), h.From_Time) AS From_Time, convert(time(0), h.To_Time) AS To_Time,
         MAX(t1.Param_Val) - MIN(t1.Param_Val) as Cal_ParamValue
  FROM   Table_1 t1
  JOIN   Hours h ON t1.Timestamp_col BETWEEN h.From_Time AND h.To_Time
  WHERE  Timestamp_col BETWEEN @start_time AND dateadd(HOUR, 24, @start_time)
  GROUP  BY h.From_Time, h.To_Time
  HAVING COUNT(*) &gt; 1
  ORDER  BY h.From_Time
  END
  </code></pre>
  
  <p>Converted SP but it is giving error as</p>
  
  <blockquote>
    <p>Unexpected End of function definition at end of input Line 17 : END$BODY$</p>
  </blockquote>
  
  <pre><code>CREATE FUNCTION sp_hourwise(OUT from_time time without time zone, OUT to_time time without time zone, OUT param_value bigint, INOUT timestamp_col timestamp without time zone) RETURNS record AS
  $BODY$DECLARE v_start_time timestamp(3) = interval '6 HOUR' + @date_param;
  BEGIN
  With Hours As (
  SELECT v_start_time + (row_number() OVER(ORDER BY from_time) - 1) *  INTERVAL '1 day' AS from_time,
  v_start_time + (row_number() OVER(ORDER BY from_time)) *  INTERVAL '1 day' AS to_time
     FROM   table_2
  )
  SELECT CAST(time(0) h.from_time), CAST(time(0) h.to_time),
         MAX(t1.param_value) - MIN(t1.param_value)
  FROM   Table_1 t1
  JOIN   Hours h ON t1.timestamp_col BETWEEN h.from_time AND h.to_time
  WHERE  timestamp_col BETWEEN v_start_time AND interval '24 HOUR' + v_start_time
  GROUP  BY h.from_time, h.to_time
  HAVING COUNT(*) &gt; 1
  ORDER  BY h.from_time 
  END$BODY$
  LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
  </code></pre>