reference/pgsql/examples.xml
aa2e1accf0888a290a76b9193844d0d7b7bd40b8
aa2e1accf0888a290a76b9193844d0d7b7bd40b8
...
...
@@ -4,6 +4,7 @@
4
4
<chapter xml:id="pgsql.examples" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink">
5
5
&reftitle.examples;
6
6
<section xml:id="pgsql.examples-basic">
7
+
<title>Basic usage</title>
7
8
<para>
8
9
This simple example shows how to connect, execute a query, print
9
10
resulting rows and disconnect from a PostgreSQL database.
...
...
@@ -12,13 +13,14 @@
12
13
<programlisting role="php">
13
14
<![CDATA[
14
15
<?php
16
+
15
17
// Connecting, selecting database
16
18
$dbconn = pg_connect("host=localhost dbname=publishing user=www password=foo")
17
19
or die('Could not connect: ' . pg_last_error());
18
20
19
21
// Performing SQL query
20
22
$query = 'SELECT * FROM authors';
21
-
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
23
+
$result = pg_query($dbconn, $query) or die('Query failed: ' . pg_last_error());
22
24
23
25
// Printing results in HTML
24
26
echo "<table>\n";
...
...
@@ -36,6 +38,81 @@ pg_free_result($result);
36
38
37
39
// Closing connection
38
40
pg_close($dbconn);
41
+
42
+
?>
43
+
]]>
44
+
</programlisting>
45
+
</example>
46
+
</para>
47
+
</section>
48
+
49
+
<section xml:id="pgsql.examples-queries">
50
+
<title>Basic usage</title>
51
+
<para>
52
+
These examples contain user defined functions similar to legacy MySQL
53
+
functions.
54
+
55
+
<example>
56
+
<title>PostgreSQL user defined functions example</title>
57
+
<programlisting role="php">
58
+
<![CDATA[
59
+
<?php
60
+
// This function should be needed, since PostgreSQL connection binds database.
61
+
function pg_list_dbs($db)
62
+
{
63
+
assert(is_resource($db));
64
+
$query = '
65
+
SELECT
66
+
d.datname as "Name",
67
+
u.usename as "Owner",
68
+
pg_encoding_to_char(d.encoding) as "Encoding"
69
+
FROM
70
+
pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid
71
+
ORDER BY 1;
72
+
';
73
+
return pg_query($db, $query);
74
+
}
75
+
76
+
// List tables.
77
+
function pg_list_tables($db)
78
+
{
79
+
assert(is_resource($db));
80
+
$query = "
81
+
SELECT
82
+
c.relname as \"Name\",
83
+
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as \"Type\",
84
+
u.usename as \"Owner\"
85
+
FROM
86
+
pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
87
+
WHERE
88
+
c.relkind IN ('r','v','S','')
89
+
AND c.relname !~ '^pg_'
90
+
ORDER BY 1;
91
+
";
92
+
return pg_query($db, $query);
93
+
}
94
+
95
+
// See also pg_meta_data(). It returns field definition as array.
96
+
function pg_list_fields($db, $table)
97
+
{
98
+
assert(is_resource($db));
99
+
$query = "
100
+
SELECT
101
+
a.attname,
102
+
format_type(a.atttypid, a.atttypmod),
103
+
a.attnotnull,
104
+
a.atthasdef,
105
+
a.attnum
106
+
FROM
107
+
pg_class c,
108
+
pg_attribute a
109
+
WHERE
110
+
c.relname = '".$table."'
111
+
AND a.attnum > 0 AND a.attrelid = c.oid
112
+
ORDER BY a.attnum;
113
+
";
114
+
return pg_query($db, $query);
115
+
}
39
116
?>
40
117
]]>
41
118
</programlisting>
42
119