source: branches/4.x/docs/contents/postgres/advanced.rst @ 746

Last change on this file since 746 was 746, checked in by cito, 4 years ago

Make references in the Postgres tutorial functional

File size: 4.6 KB
Line 
1Examples for advanced features
2==============================
3
4.. py:currentmodule:: pg
5
6In this section, we show how to use some advanced features of PostgreSQL
7using the classic PyGreSQL interface.
8
9We assume that you have already created a connection to the PostgreSQL
10database, as explained in the :doc:`basic`::
11
12    >>> from pg import DB
13    >>> db = DB()
14    >>> query = query
15
16Inheritance
17-----------
18
19A table can inherit from zero or more tables. A query can reference either
20all rows of a table or all rows of a table plus all of its descendants.
21
22For example, the capitals table inherits from cities table (it inherits
23all data fields from cities)::
24
25    >>> data = [('cities', [
26    ...         "'San Francisco', 7.24E+5, 63",
27    ...         "'Las Vegas', 2.583E+5, 2174",
28    ...         "'Mariposa', 1200, 1953"]),
29    ...     ('capitals', [
30    ...         "'Sacramento',3.694E+5,30,'CA'",
31    ...         "'Madison', 1.913E+5, 845, 'WI'"])]
32
33Now, let's populate the tables::
34
35    >>> data = ['cities', [
36    ...         "'San Francisco', 7.24E+5, 63"
37    ...         "'Las Vegas', 2.583E+5, 2174"
38    ...         "'Mariposa', 1200, 1953"],
39    ...     'capitals', [
40    ...         "'Sacramento',3.694E+5,30,'CA'",
41    ...         "'Madison', 1.913E+5, 845, 'WI'"]]
42    >>> for table, rows in data:
43    ...     for row in rows:
44    ...         query("INSERT INTO %s VALUES (%s)" % (table, row))
45    >>> print query("SELECT * FROM cities")
46        name     |population|altitude
47    -------------+----------+--------
48    San Francisco|    724000|      63
49    Las Vegas    |    258300|    2174
50    Mariposa     |      1200|    1953
51    Sacramento   |    369400|      30
52    Madison      |    191300|     845
53    (5 rows)
54    >>> print query("SELECT * FROM capitals")
55       name   |population|altitude|state
56    ----------+----------+--------+-----
57    Sacramento|    369400|      30|CA
58    Madison   |    191300|     845|WI
59    (2 rows)
60
61You can find all cities, including capitals, that are located at an altitude
62of 500 feet or higher by::
63
64    >>> print query("""SELECT c.name, c.altitude
65    ...     FROM cities
66    ...     WHERE altitude > 500""")
67      name   |altitude
68    ---------+--------
69    Las Vegas|    2174
70    Mariposa |    1953
71    Madison  |     845
72    (3 rows)
73
74On the other hand, the following query references rows of the base table only,
75i.e. it finds all cities that are not state capitals and are situated at an
76altitude of 500 feet or higher::
77
78    >>> print query("""SELECT name, altitude
79    ...     FROM ONLY cities
80    ...     WHERE altitude > 500""")
81      name   |altitude
82    ---------+--------
83    Las Vegas|    2174
84    Mariposa |    1953
85    (2 rows)
86
87Arrays
88------
89
90Attributes can be arrays of base types or user-defined types::
91
92    >>> query("""CREATE TABLE sal_emp (
93    ...        name                  text,
94    ...        pay_by_quarter        int4[],
95    ...        pay_by_extra_quarter  int8[],
96    ...        schedule              text[][])""")
97
98
99Insert instances with array attributes. Note the use of braces::
100
101    >>> query("""INSERT INTO sal_emp VALUES (
102    ...     'Bill', '{10000,10000,10000,10000}',
103    ...     '{9223372036854775800,9223372036854775800,9223372036854775800}',
104    ...     '{{"meeting", "lunch"}, {"training", "presentation"}}')""")
105    >>> query("""INSERT INTO sal_emp VALUES (
106    ...     'Carol', '{20000,25000,25000,25000}',
107    ...      '{9223372036854775807,9223372036854775807,9223372036854775807}',
108    ...      '{{"breakfast", "consulting"}, {"meeting", "lunch"}}')""")
109
110
111Queries on array attributes::
112
113    >>> query("""SELECT name FROM sal_emp WHERE
114    ...     sal_emp.pay_by_quarter[1] != sal_emp.pay_by_quarter[2]""")
115    name
116    -----
117    Carol
118    (1 row)
119
120Retrieve third quarter pay of all employees::
121
122    >>> query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp")
123    pay_by_quarter
124    --------------
125             10000
126             25000
127    (2 rows)
128
129Retrieve third quarter extra pay of all employees::
130
131    >>> query("SELECT sal_emp.pay_by_extra_quarter[3] FROM sal_emp")
132    pay_by_extra_quarter
133    --------------------
134     9223372036854775800
135     9223372036854775807
136    (2 rows)
137
138Retrieve first two quarters of extra quarter pay of all employees::
139
140    >>> query("SELECT sal_emp.pay_by_extra_quarter[1:2] FROM sal_emp")
141              pay_by_extra_quarter
142    -----------------------------------------
143    {9223372036854775800,9223372036854775800}
144    {9223372036854775807,9223372036854775807}
145    (2 rows)
146
147Select subarrays::
148
149    >>> query("""SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp
150    ...     WHERE sal_emp.name = 'Bill'""")
151           schedule
152    ----------------------
153    {{meeting},{training}}
154    (1 row)
Note: See TracBrowser for help on using the repository browser.