reference/pgsql/examples.xml
cffb5f52894aa8a29109e3802e2257c56b51bcb6
...
...
@@ -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.
...
...
@@ -42,6 +43,80 @@ pg_close($dbconn);
42
43
</example>
43
44
</para>
44
45
</section>
46
+

47
+
<section xml:id="pgsql.examples-queries">
48
+
<title>Basic usage</title>
49
+
<para>
50
+
These examples contain user defined functions similar to legacy MySQL
51
+
functions.
52
+

53
+
<example>
54
+
<title>PostgreSQL user defined functions example</title>
55
+
<programlisting role="php">
56
+
<![CDATA[
57
+
<?php
58
+
// This function should be needed, since PostgreSQL connection binds database.
59
+
function pg_list_dbs($db)
60
+
{
61
+
assert(is_resource($db));
62
+
$query = '
63
+
SELECT
64
+
d.datname as "Name",
65
+
u.usename as "Owner",
66
+
pg_encoding_to_char(d.encoding) as "Encoding"
67
+
FROM
68
+
pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid
69
+
ORDER BY 1;
70
+
';
71
+
return pg_query($db, $query);
72
+
}
73
+

74
+
// List tables.
75
+
function pg_list_tables($db)
76
+
{
77
+
assert(is_resource($db));
78
+
$query = "
79
+
SELECT
80
+
c.relname as \"Name\",
81
+
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\",
82
+
u.usename as \"Owner\"
83
+
FROM
84
+
pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
85
+
WHERE
86
+
c.relkind IN ('r','v','S','')
87
+
AND c.relname !~ '^pg_'
88
+
ORDER BY 1;
89
+
";
90
+
return pg_query($db, $query);
91
+
}
92
+

93
+
// See also pg_meta_data(). It returns field definition as array.
94
+
function pg_list_fields($db, $table)
95
+
{
96
+
assert(is_resource($db));
97
+
$query = "
98
+
SELECT
99
+
a.attname,
100
+
format_type(a.atttypid, a.atttypmod),
101
+
a.attnotnull,
102
+
a.atthasdef,
103
+
a.attnum
104
+
FROM
105
+
pg_class c,
106
+
pg_attribute a
107
+
WHERE
108
+
c.relname = '".$table."'
109
+
AND a.attnum > 0 AND a.attrelid = c.oid
110
+
ORDER BY a.attnum;
111
+
";
112
+
return pg_query($db, $query);
113
+
}
114
+
?>
115
+
]]>
116
+
</programlisting>
117
+
</example>
118
+
</para>
119
+
</section>
45
120
</chapter>
46
121

47
122
<!-- Keep this comment at the end of the file
48
123