File: func.rst.txt

package info (click to toggle)
pygresql 1%3A5.0.6-2
  • links: PTS, VCS
  • area: main
  • in suites: buster
  • size: 3,432 kB
  • sloc: python: 13,318; ansic: 4,984; makefile: 164
file content (162 lines) | stat: -rw-r--r-- 4,945 bytes parent folder | download | duplicates (4)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
Examples for using SQL functions
================================

.. py: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("INSERT INTO EMP VALUES (%s)" % 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 project a column out of resulting the 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")