do language plpgsql$$declare dz driver_zone%rowtype; leadtime integer;begin leadtime = 0; for dz in select * from driver_zone loop leadtime = leadtime + 1; update driver_zone set lead_time = leadtime where id = dz.id; end loop;end $$;
===============================================
CREATE OR REPLACE FUNCTION deliver_orders() RETURNS integer AS$BODY$DECLARE cnt integer := 0; rev consolidated_order_revenue%rowtype; firstOrder _order%rowtype; sellingCount integer := 0; buyingCount integer := 0;BEGIN FOR rev IN select distinct * from consolidated_order_revenue where id in (select buying_consolidation_id from _order where created_on > '2013-11-01') LOOPSELECT * INTO firstOrder FROM _order WHERE buying_consolidation_id = rev.id;SELECT COUNT(*) INTO buyingCount FROM _order WHERE buying_consolidation_id = firstOrder.buying_consolidation_id;SELECT COUNT(*) INTO sellingCount FROM _order WHERE selling_consolidation_id = firstOrder.selling_consolidation_id;IF sellingCount > 0 AND buyingCount <> sellingCount THEN RAISE NOTICE 'Fixing revenue: % (buying %, selling %)', rev.id, buyingCount, sellingCount;END IF; END LOOP; RAISE NOTICE 'Delivered % orders', cnt; RETURN cnt;END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100;ALTER FUNCTION deliver_orders() OWNER TO jetty;
转载于:https://www.cnblogs.com/Uncho/p/4949002.html
相关资源：字符串分割成数组函数（plsql function）