N
โdก4c       s      d  GH d GH H d GH	 H
 d GH d GH d GH d  GH k  l  k l  d   Z < d   Z  d   Z ณ d	   Z ล d
   Z d S(   sB   __________________________________________________________________s1   MODULE FUNC.PY : SQL FUNCTION DEFINITION TUTORIALs=   This module is designed for being imported from python promptsD   In order to run the samples included here, first create a connections6   using :                        cnx = func.connect(...)s-   then start the demo with:      func.demo(cnx)c    sY    d GH d GH d GH d GH d GH d GH d GH H d GH d GH d	 GH H  d
 GH! d GH" |  i d  $ t   % H& d GH' d GH( d GH) H* d GH+ |  i d  } , t | i   | i    - H. d GH/ d GH0 d GH1 H2 d GH3 d GH4 |  i d  6 H7 d GH8 |  i d  } 9 t | i   | i    d  S(   Ns   -----------------------------s'   -- Creating SQL Functions on Base Typess5   --	a CREATE FUNCTION statement lets you create a new s8   --	function that can be used in expressions (in SELECT, s4   --	INSERT, etc.). We will start with functions that s   --	return values of base types.s   --s=   -- let's create a simple SQL function that takes no argumentss   -- and returns 1s"   CREATE FUNCTION one() RETURNS int4s&      AS 'SELECT 1 as ONE' LANGUAGE 'sql'sF   CREATE FUNCTION one() RETURNS int4 AS 'SELECT 1 as ONE' LANGUAGE 'sql's>   -- functions can be used in any expressions (eg. in the targets   -- list or qualifications)s   SELECT one() AS answers=   -- here's how you create a function that takes arguments. Thes;   -- following function returns the sum of its two arguments:s/   CREATE FUNCTION add_em(int4, int4) RETURNS int4s%      AS 'SELECT $1 + $2' LANGUAGE 'sql'sR   CREATE FUNCTION add_em(int4, int4) RETURNS int4 AS 'SELECT $1 + $2' LANGUAGE 'sql's   SELECT add_em(1, 2) AS answer(   s   pgcnxs   querys   wait_keys   qs   displays
   listfieldss	   getresult(   s   pgcnxs   qs   func.pys	   base_func sH   
c    sล  < = d GH> d GH? d GH@ d GHA d GHB HC d GHD d GHE HF d GHG d GHH d	 GHI d
 GHJ d GHK d GHL |  i d  Q HR d GHS d GHT d GHU d GHV d GHW |  i d  X |  i d  Y |  i d  Z |  i d  [ |  i d  \ t   ] H^ d GH_ d GH` Ha d GHb d GHc |  i d  e Hf d GHg d GHh d GHi |  i d  } k t | i   | i    l Hm d GHn d GHo d GHp Hq d GHr d  GHs d! GHt d" GHu d# GHv d$ GHw |  i d%  } t   ~ H d& GH d' GH d( GH d) GH H d* GH |  i d*  }  t | i   | i     H d+ GH d, GH d- GH d$ GH |  i d.   H d/ GH |  i d/  }  t | i   | i    d  S(0   Ns   -----------------------------s,   -- Creating SQL Functions on Composite Typess6   --	it is also possible to create functions that returns   --	values of composite types.s8   -- before we create more sophisticated functions, let's s   -- populate an EMP tables   CREATE TABLE EMP (s   	   name		text,s       salary	int4,s       age		int4,s       dept		char16s   )sA   CREATE TABLE EMP (name		text,salary		int4,age		int4,dept		char16)s/   INSERT INTO EMP VALUES ('Sam', 1200, 16, 'toy')s3   INSERT INTO EMP VALUES ('Claire', 5000, 32, 'shoe')s2   INSERT INTO EMP VALUES ('Andy', -1000, 2, 'candy')s1   INSERT INTO EMP VALUES ('Bill', 4200, 36, 'shoe')s4   INSERT INTO EMP VALUES ('Ginger', 4800, 30, 'candy')s7   -- the argument of a function can also be a tuple. For s9   -- instance, double_salary takes a tuple of the EMP tables/   CREATE FUNCTION double_salary(EMP) RETURNS int4s5      AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql'sb   CREATE FUNCTION double_salary(EMP) RETURNS int4 AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql's(   SELECT name, double_salary(EMP) AS dreams   FROM EMPs   WHERE EMP.dept = 'toy'sH   SELECT name, double_salary(EMP) AS dream FROM EMP WHERE EMP.dept = 'toy's?   -- the return value of a function can also be a tuple. However,s?   -- make sure that the expressions in the target list is in the s$   -- same order as the columns of EMP.s%   CREATE FUNCTION new_emp() RETURNS EMPs#      AS 'SELECT 'None'::text AS name,s                 1000 AS salary,s                 25 AS age,s%                 'none'::char16 AS dept's      LANGUAGE 'sql's   CREATE FUNCTION new_emp() RETURNS EMP AS 'SELECT \'None\'::text AS name, 1000 AS salary, 25 AS age, \'none\'::char16 AS dept' LANGUAGE 'sql's>   -- you can then project a column out of resulting the tuple bys9   -- using the "function notation" for projection columns. s=   -- (ie. bar(foo) is equivalent to foo.bar) Note that we don'ts)   -- support new_emp().name at this moment.s    SELECT name(new_emp()) AS nobodys2   -- let's try one more function that returns tupless,   CREATE FUNCTION high_pay() RETURNS setof EMPs-      AS 'SELECT * FROM EMP where salary > 1500'sf   CREATE FUNCTION high_pay() RETURNS setof EMP AS 'SELECT * FROM EMP where salary > 1500' LANGUAGE 'sql's#   SELECT name(high_pay()) AS overpaid(   s   pgcnxs   querys   wait_keys   qs   displays
   listfieldss	   getresult(   s   pgcnxs   qs   func.pys	   comp_func< s   

c    s-    d GH d GH d GH d GH d GH H d GH d GH d GH H  d GHก |  i d  } ข t | i   | i    ฃ Hค d	 GHฅ d
 GHฆ d GHง d GHจ |  i d  ฉ Hช d GHซ |  i d  } ฌ t | i   | i    ญ Hฎ d GHฏ |  i d  } ฐ t | i   | i    d  S(   Ns   -----------------------------s6   -- Creating SQL Functions with multiple SQL statementss9   --	you can also create functions that do more than just as
   --	SELECT.s>   -- you may have noticed that Andy has a negative salary. We'lls:   -- create a function that removes employees with negative s   -- salaries.s   SELECT * FROM EMPs)   CREATE FUNCTION clean_EMP () RETURNS int4s,      AS 'DELETE FROM EMP WHERE EMP.salary <= 0s          SELECT 1 AS ignore_this's      LANGUAGE 'sql's|   CREATE FUNCTION clean_EMP () RETURNS int4 AS 'DELETE FROM EMP WHERE EMP.salary <= 0; SELECT 1 AS ignore_this' LANGUAGE 'sql's   SELECT clean_EMP()(   s   pgcnxs   querys   qs   displays
   listfieldss	   getresult(   s   pgcnxs   qs   func.pys	   mult_func s6   c    sง   ณ ด d GHต Hถ d GHท d GHธ d GHน d GHบ d GHป Hผ d GHฝ |  i d  พ |  i d  ฟ |  i d  ภ |  i d  ม |  i d  ย |  i d  d  S(   Ns2   -- remove functions that were created in this files   DROP FUNCTION clean_EMP()s   DROP FUNCTION high_pay()s   DROP FUNCTION new_emp()s    DROP FUNCTION add_em(int4, int4)s   DROP FUNCTION one()s   DROP TABLE EMP(   s   pgcnxs   query(   s   pgcnxs   func.pys   demo_cleanupณ s   c    s;   ล ฦ t  |   ว t |   ศ t |   ษ t |   d  S(   N(   s	   base_funcs   pgcnxs	   comp_funcs	   mult_funcs   demo_cleanup(   s   pgcnxs   func.pys   demoล s   N(   s   pgtoolss   *s   pgexts	   base_funcs	   comp_funcs	   mult_funcs   demo_cleanups   demo(    s   func.pys   ? s   

)Y