Examples for using SQL functions ================================ .. currentmodule:: pg We assume that you have already created a connection to the PostgreSQL database, as explained in the :doc:`basic`:: >>> from pg import DB >>> db = DB() >>> query = db.query Creating SQL Functions on Base Types ------------------------------------ A **CREATE FUNCTION** statement lets you create a new function that can be used in expressions (in SELECT, INSERT, etc.). We will start with functions that return values of base types. Let's create a simple SQL function that takes no arguments and returns 1:: >>> query("""CREATE FUNCTION one() RETURNS int4 ... AS 'SELECT 1 as ONE' LANGUAGE SQL""") Functions can be used in any expressions (eg. in the target list or qualifications):: >>> print(db.query("SELECT one() AS answer")) answer ------ 1 (1 row) Here's how you create a function that takes arguments. The following function returns the sum of its two arguments:: >>> query("""CREATE FUNCTION add_em(int4, int4) RETURNS int4 ... AS $$ SELECT $1 + $2 $$ LANGUAGE SQL""") >>> print(query("SELECT add_em(1, 2) AS answer")) answer ------ 3 (1 row) Creating SQL Functions on Composite Types ----------------------------------------- It is also possible to create functions that return values of composite types. Before we create more sophisticated functions, let's populate an EMP table:: >>> query("""CREATE TABLE EMP ( ... name text, ... salary int4, ... age f int4, ... dept varchar(16))""") >>> emps = ["'Sam', 1200, 16, 'toy'", ... "'Claire', 5000, 32, 'shoe'", ... "'Andy', -1000, 2, 'candy'", ... "'Bill', 4200, 36, 'shoe'", ... "'Ginger', 4800, 30, 'candy'"] >>> for emp in emps: ... query(f"INSERT INTO EMP VALUES ({emp})") Every INSERT statement will return a '1' indicating that it has inserted one row into the EMP table. The argument of a function can also be a tuple. For instance, *double_salary* takes a tuple of the EMP table:: >>> query("""CREATE FUNCTION double_salary(EMP) RETURNS int4 ... AS $$ SELECT $1.salary * 2 AS salary $$ LANGUAGE SQL""") >>> print(query("""SELECT name, double_salary(EMP) AS dream ... FROM EMP WHERE EMP.dept = 'toy'""")) name|dream ----+----- Sam | 2400 (1 row) The return value of a function can also be a tuple. However, make sure that the expressions in the target list are in the same order as the columns of EMP:: >>> 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""") You can then extract a column out of the resulting tuple by using the "function notation" for projection columns (i.e. ``bar(foo)`` is equivalent to ``foo.bar``). Note that ``new_emp().name`` isn't supported:: >>> print(query("SELECT name(new_emp()) AS nobody")) nobody ------ None (1 row) Let's try one more function that returns tuples:: >>> query("""CREATE FUNCTION high_pay() RETURNS setof EMP ... AS 'SELECT * FROM EMP where salary > 1500' ... LANGUAGE SQL""") >>> query("SELECT name(high_pay()) AS overpaid") overpaid -------- Claire Bill Ginger (3 rows) Creating SQL Functions with multiple SQL statements --------------------------------------------------- You can also create functions that do more than just a SELECT. You may have noticed that Andy has a negative salary. We'll create a function that removes employees with negative salaries:: >>> query("SELECT * FROM EMP") name |salary|age|dept ------+------+---+----- Sam | 1200| 16|toy Claire| 5000| 32|shoe Andy | -1000| 2|candy Bill | 4200| 36|shoe Ginger| 4800| 30|candy (5 rows) >>> query("""CREATE FUNCTION clean_EMP () RETURNS int4 AS ... 'DELETE FROM EMP WHERE EMP.salary < 0; ... SELECT 1 AS ignore_this' ... LANGUAGE SQL""") >>> query("SELECT clean_EMP()") clean_emp --------- 1 (1 row) >>> query("SELECT * FROM EMP") name |salary|age|dept ------+------+---+----- Sam | 1200| 16|toy Claire| 5000| 32|shoe Bill | 4200| 36|shoe Ginger| 4800| 30|candy (4 rows) Remove functions that were created in this example -------------------------------------------------- We can remove the functions that we have created in this example and the table EMP, by using the DROP command:: query("DROP FUNCTION clean_EMP()") query("DROP FUNCTION high_pay()") query("DROP FUNCTION new_emp()") query("DROP FUNCTION add_em(int4, int4)") query("DROP FUNCTION one()") query("DROP TABLE EMP CASCADE")