Add missing python files.

1 files changed, 205 insertions, 0 deletions

diff --git a/src/interfaces/python/tutorial/func.py b/src/interfaces/python/tutorial/func.py
new file mode 100755
index 00000000000..4ae1876e044
--- /dev/null
+++ b/src/interfaces/python/tutorial/func.py
@@ -0,0 +1,205 @@
+# func.py - demonstrate the use of SQL functions
+# inspired from the PostgreSQL tutorial 
+# adapted to Python 1995 by Pascal ANDRE
+
+print """
+__________________________________________________________________
+MODULE FUNC.PY : SQL FUNCTION DEFINITION TUTORIAL
+
+This module is designed for being imported from python prompt
+
+In order to run the samples included here, first create a connection
+using :                        cnx = advanced.DB(...)
+
+The "..." should be replaced with whatever arguments you need to open an
+existing database.  Usually all you need is the name of the database and,
+in fact, if it is the same as your login name, you can leave it empty.
+
+then start the demo with:      func.demo(cnx)
+__________________________________________________________________
+""" 
+
+from pg import DB
+import sys
+
+# waits for a key
+def wait_key():
+	print "Press <enter>"
+	sys.stdin.read(1)
+
+# basic functions declaration
+def base_func(pgcnx):
+	print "-----------------------------"
+	print "-- Creating SQL Functions on Base Types"
+	print "--  a CREATE FUNCTION statement lets you create a new "
+	print "--  function that can be used in expressions (in SELECT, "
+	print "--  INSERT, etc.). We will start with functions that "
+	print "--  return values of base types."
+	print "-----------------------------"
+	print
+	print "--"
+	print "-- let's create a simple SQL function that takes no arguments"
+	print "-- and returns 1"
+	print
+	print "CREATE FUNCTION one() RETURNS int4"
+	print "   AS 'SELECT 1 as ONE' LANGUAGE 'sql'"
+	pgcnx.query("""CREATE FUNCTION one() RETURNS int4
+        AS 'SELECT 1 as ONE' LANGUAGE 'sql'""")
+	wait_key()
+	print
+	print "--"
+	print "-- functions can be used in any expressions (eg. in the target"
+	print "-- list or qualifications)"
+	print
+	print "SELECT one() AS answer"
+	print pgcnx.query("SELECT one() AS answer")
+	print
+	print "--"
+	print "-- here's how you create a function that takes arguments. The"
+	print "-- following function returns the sum of its two arguments:"
+	print
+	print "CREATE FUNCTION add_em(int4, int4) RETURNS int4"
+	print "   AS 'SELECT $1 + $2' LANGUAGE 'sql'"
+	pgcnx.query("""CREATE FUNCTION add_em(int4, int4) RETURNS int4
+        AS 'SELECT $1 + $2' LANGUAGE 'sql'""")
+	print
+	print "SELECT add_em(1, 2) AS answer"
+	print pgcnx.query("SELECT add_em(1, 2) AS answer")
+
+# functions on composite types
+def comp_func(pgcnx):
+	print "-----------------------------"
+	print "-- Creating SQL Functions on Composite Types"
+	print "--  it is also possible to create functions that return"
+	print "--  values of composite types."
+	print "-----------------------------"
+	print
+	print "-- before we create more sophisticated functions, let's "
+	print "-- populate an EMP table"
+	print
+	print "CREATE TABLE EMP ("
+	print "    name    text,"
+	print "    salary  int4,"
+	print "    age     int4,"
+	print "    dept    varchar(16)"
+	print ")"
+	pgcnx.query("""CREATE TABLE EMP (
+        name        text,
+        salary      int4,
+        age         int4,
+        dept        varchar(16))""")
+	print
+	print "INSERT INTO EMP VALUES ('Sam', 1200, 16, 'toy')"
+	print "INSERT INTO EMP VALUES ('Claire', 5000, 32, 'shoe')"
+	print "INSERT INTO EMP VALUES ('Andy', -1000, 2, 'candy')"
+	print "INSERT INTO EMP VALUES ('Bill', 4200, 36, 'shoe')"
+	print "INSERT INTO EMP VALUES ('Ginger', 4800, 30, 'candy')"
+	pgcnx.query("INSERT INTO EMP VALUES ('Sam', 1200, 16, 'toy')")
+	pgcnx.query("INSERT INTO EMP VALUES ('Claire', 5000, 32, 'shoe')")
+	pgcnx.query("INSERT INTO EMP VALUES ('Andy', -1000, 2, 'candy')")
+	pgcnx.query("INSERT INTO EMP VALUES ('Bill', 4200, 36, 'shoe')")
+	pgcnx.query("INSERT INTO EMP VALUES ('Ginger', 4800, 30, 'candy')")
+	wait_key()
+	print
+	print "-- the argument of a function can also be a tuple. For "
+	print "-- instance, double_salary takes a tuple of the EMP table"
+	print
+	print "CREATE FUNCTION double_salary(EMP) RETURNS int4"
+	print "   AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql'"
+	pgcnx.query("""CREATE FUNCTION double_salary(EMP) RETURNS int4
+        AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql'""")
+	print
+	print "SELECT name, double_salary(EMP) AS dream"
+	print "FROM EMP"
+	print "WHERE EMP.dept = 'toy'"
+	print pgcnx.query("""SELECT name, double_salary(EMP) AS dream
+        FROM EMP WHERE EMP.dept = 'toy'""")
+	print
+	print "-- the return value of a function can also be a tuple. However,"
+	print "-- make sure that the expressions in the target list is in the "
+	print "-- same order as the columns of EMP."
+	print
+	print "CREATE FUNCTION new_emp() RETURNS EMP"
+	print "   AS 'SELECT \'None\'::text AS name,"
+	print "              1000 AS salary,"
+	print "              25 AS age,"
+	print "              \'none\'::varchar(16) AS dept'"
+	print "   LANGUAGE 'sql'"
+	pgcnx.query("""CREATE FUNCTION new_emp() RETURNS EMP
+        AS 'SELECT \\\'None\\\'::text AS name,
+            1000 AS salary,
+            25 AS age,
+            \\\'none\\\'::varchar(16) AS dept'
+        LANGUAGE 'sql'""")
+	wait_key()
+	print
+	print "-- you can then project a column out of resulting the tuple by"
+	print "-- using the \"function notation\" for projection columns. "
+	print "-- (ie. bar(foo) is equivalent to foo.bar) Note that we don't"
+	print "-- support new_emp().name at this moment."
+	print
+	print "SELECT name(new_emp()) AS nobody"
+	print pgcnx.query("SELECT name(new_emp()) AS nobody")
+	print
+	print "-- let's try one more function that returns tuples"
+	print "CREATE FUNCTION high_pay() RETURNS setof EMP"
+	print "   AS 'SELECT * FROM EMP where salary > 1500'"
+	print "   LANGUAGE 'sql'"
+	pgcnx.query("""CREATE FUNCTION high_pay() RETURNS setof EMP
+        AS 'SELECT * FROM EMP where salary > 1500'
+        LANGUAGE 'sql'""")
+	print
+	print "SELECT name(high_pay()) AS overpaid"
+	print pgcnx.query("SELECT name(high_pay()) AS overpaid")
+
+# function with multiple SQL commands
+def mult_func(pgcnx):
+	print "-----------------------------"
+	print "-- Creating SQL Functions with multiple SQL statements"
+	print "--  you can also create functions that do more than just a"
+	print "--  SELECT."
+	print "-----------------------------"
+	print
+	print "-- you may have noticed that Andy has a negative salary. We'll"
+	print "-- create a function that removes employees with negative "
+	print "-- salaries."
+	print
+	print "SELECT * FROM EMP"
+	print pgcnx.query("SELECT * FROM EMP")
+	print
+	print "CREATE FUNCTION clean_EMP () RETURNS int4"
+	print "   AS 'DELETE FROM EMP WHERE EMP.salary <= 0"
+	print "       SELECT 1 AS ignore_this'"
+	print "   LANGUAGE 'sql'"
+	pgcnx.query("CREATE FUNCTION clean_EMP () RETURNS int4 AS 'DELETE FROM EMP WHERE EMP.salary <= 0; SELECT 1 AS ignore_this' LANGUAGE 'sql'")
+	print
+	print "SELECT clean_EMP()"
+	print pgcnx.query("SELECT clean_EMP()")
+	print
+	print "SELECT * FROM EMP"
+	print pgcnx.query("SELECT * FROM EMP")
+
+# base cleanup
+def demo_cleanup(pgcnx):
+	print "-- remove functions that were created in this file"
+	print
+	print "DROP FUNCTION clean_EMP()"
+	print "DROP FUNCTION high_pay()"
+	print "DROP FUNCTION new_emp()"
+	print "DROP FUNCTION add_em(int4, int4)"
+	print "DROP FUNCTION one()"
+	print
+	print "DROP TABLE EMP"
+	pgcnx.query("DROP FUNCTION clean_EMP()")
+	pgcnx.query("DROP FUNCTION high_pay()")
+	pgcnx.query("DROP FUNCTION new_emp()")
+	pgcnx.query("DROP FUNCTION add_em(int4, int4)")
+	pgcnx.query("DROP FUNCTION one()")
+	pgcnx.query("DROP TABLE EMP")
+
+# main demo function
+def demo(pgcnx):
+	base_func(pgcnx)
+	comp_func(pgcnx)
+	mult_func(pgcnx)
+	demo_cleanup(pgcnx)