title: Error while creating a stored procedure in PostgreSQL
body: <p>I need to create a stored procedure in PostgreSQL database for getting the records,but i'm getting the below error.</p>
  
  <p>Any suggestions on how to resolve it?  </p>
  
  <p>Note: Ultimately i want this stored procedure to be called inside PYTHON script and get all records and process it inside the python and Insert it into a remote MySQL database. </p>
  
  <pre><code>ERROR:  syntax error at or near "("
  LINE 7:  WITH reference AS (select cast(origin as text) as origin, M...
                ^
  CONTEXT:  invalid type name "reference AS (select cast(origin as text) as origin, MAX(case when name like '%PICK%' THEN name else null end) as "Pick_ref"
                                                    , MAX(case when name like '%OUT%' THEN name else null end) as "Pack_ref"
                                                    , MAX(case when name like '%PICK%' THEN state else null end) as "Pick_State"
                                                   , MAX(case when name like '%OUT%' THEN state else null end) as "Pack_State"
  
                      , MAX(case when name like '%PICK%' THEN date_done else null end) as "Pick_State_Done"
                      , MAX(case when name like '%OUT%' THEN date_done else null end) as "Pack_State_Done"
                       from dl_odoo.stock_picking  
                                   GROUP BY origin)
  "
  SQL state: 42601
  Character: 127
  </code></pre>
  
  <p>This is the entire SP code which i need to create,</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE public.sync( )
  LANGUAGE 'plpgsql'
  
  AS $BODY$
  DECLARE  start_time              TIMESTAMPTZ;
   WITH reference AS (select cast(origin as text) as origin, MAX(case when name like '%PICK%' THEN name else null end) as "Pick_ref"
                                                    , MAX(case when name like '%OUT%' THEN name else null end) as "Pack_ref"
                                                    , MAX(case when name like '%PICK%' THEN state else null end) as "Pick_State"
                                                   , MAX(case when name like '%OUT%' THEN state else null end) as "Pack_State"
  
                      , MAX(case when name like '%PICK%' THEN date_done else null end) as "Pick_State_Done"
                      , MAX(case when name like '%OUT%' THEN date_done else null end) as "Pack_State_Done"
                       from dl_odoo.stock_picking  
                                   GROUP BY origin)
  ,tracking_details as
  (
   select magento_order_number,tracking_number,tracking_link,send_at from  staging.hermes_tracking_report
   UNION
   select magento_order_number,tracking_number,tracking_link,send_at from  staging.dhl_tracking_report
  )
  ,temp_out as
  (
  SELECT   DISTINCT   o.order_id,o.order_number AS "order_number",o.customer_email
            ,sp.sale_id as "Odoo Sale ID"
            ,COALESCE(rp.name,c.first_name||' '||c.last_name) customer_name
            ,o.created_at_order "Order_date"
            ,o.order_state_1 "order_state_1"
            ,o.order_state_2 "order_state_2"
            ,o.payment_method,o.order_subtotal
            ,o.shipping_country_id
            ,COALESCE(dc.name,o.shipping_description ) as "Carrier"
            ,COALESCE(so.date_order) AS "Odoo_Order_date",
            COALESCE(so.mage_state) AS "Odoo_Mage_state",
            COALESCE(so.mage_status) AS "Odoo_Mage_status",
            CASE
                WHEN o.invoice_state=1 THEN 'OPEN'
                WHEN o.invoice_state=2 THEN 'PAID'
                WHEN o.invoice_state=3 THEN 'CANCELED'
                ELSE ''
            END AS "Mage_Invoice_state",
            COALESCE(so.invoice_status ) AS "Odoo_Invoice_status"
  
           ,so.state as "odoo_sale_state"
           ,"Pick_ref","Pack_ref","Pick_State",r."Pick_State_Done","Pack_State",r."Pack_State_Done"
           ,td.tracking_number,td.tracking_link,td.send_at as "Tracking_info_send_at"
           ,CASE WHEN 
                ("Pick_State" ='done'and "Pack_State"='done') 
                AND 
                (o.order_state_1 &lt;&gt;'complete'and o.order_state_2&lt;&gt;'complete') 
               THEN 'NEED TO SYNC' 
               WHEN 
                ("Pick_State" &lt;&gt;'done' Or "Pack_State"&lt;&gt;'done') 
                AND (o.order_state_1 &lt;&gt;'complete'and o.order_state_2&lt;&gt;'complete') 
                THEN 'Stll Processing'
               END as "SYNC Status"
      ,0 as shipment_status
  FROM ol.orders o
     LEFT JOIN dl_odoo.sale_order so ON 
     o.order_number= so.name 
     LEFT JOIN ol.customers c on c.customer_email=o.customer_email
     LEFT JOIN dl_odoo.res_partner rp on rp.id=so.partner_id
     LEFT JOIN dl_odoo.sale_order so_2 ON o.order_number=so_2.name
     LEFT JOIN dl_odoo.sale_order_line sol ON sol.id=so.id
     LEFT JOIN dl_odoo.delivery_carrier dc on so.carrier_id=dc.id
     LEFT JOIN  dl_odoo.stock_picking sp on o.order_number=sp.origin
     LEFT JOIN reference r on r.origin=o.order_number
     LEFT JOIN tracking_details td on td.magento_order_number=o.order_number
     WHERE TO_CHAR("o"."created_at_order" ,'YYYY-MM') BETWEEN TO_CHAR(now() - INTERVAL '44 days','YYYY-MM') AND TO_CHAR(now(),'YYYY-MM')
     AND "o"."order_state_2"&lt;&gt;'canceled'
              AND "o"."order_state_2" &lt;&gt; 'pending_payment'
              AND "o"."order_state_1" &lt;&gt; 'canceled'
              AND "o"."order_state_1" &lt;&gt; 'pending_payment'
              AND "o"."order_state_1" &lt;&gt; 'closed'
              AND "o"."order_state_2" &lt;&gt; 'closed'
              AND "o".payment_method ILIKE '%bankpayment%'
    ) 
   ,odoo_cancel as
   (
   SELECT  "order_id",'canceled' as status
                    ,'canceled' as state
   FROM temp_out
   WHERE odoo_sale_state='cancel' 
       )
  ,odoo_complete as
   (
   SELECT  "order_id",'complete' as status
                     ,'complete' as state
                     ,tracking_number,shipment_status
   FROM temp_out
   WHERE "Pack_State"='done'
       )
  SELECT  'cancel' as state, now() as created_at,'{"action":"updateOrder",' || json_build_object('data',row_to_json(row)) || '}'  
  from (select * from odoo_cancel) row  as payload
  
  UNION
  
  SELECT 'complete' as state,now() as created_at,'{"action":"updateOrder",' || json_build_object('data',row_to_json(row)) || '}'  
  from (select * from odoo_complete) row 
  
  END
  $BODY$;
  </code></pre>