diff options
Diffstat (limited to 'src/interfaces/python/tutorial/func.py')
| -rwxr-xr-x | src/interfaces/python/tutorial/func.py | 205 |
1 files changed, 0 insertions, 205 deletions
diff --git a/src/interfaces/python/tutorial/func.py b/src/interfaces/python/tutorial/func.py deleted file mode 100755 index af2b412bdfc..00000000000 --- a/src/interfaces/python/tutorial/func.py +++ /dev/null @@ -1,205 +0,0 @@ -# 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 = func.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 CASCADE" - 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 CASCADE") - -# main demo function -def demo(pgcnx): - base_func(pgcnx) - comp_func(pgcnx) - mult_func(pgcnx) - demo_cleanup(pgcnx) |