Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit53ae7ac

Browse files
committed
Add SGML documentation for contrib/spi and contrib/test_parser.
The spi documentation is pretty rudimentary, but it's a start.
1 parent0d4c385 commit53ae7ac

File tree

4 files changed

+308
-2
lines changed

4 files changed

+308
-2
lines changed

‎doc/src/sgml/contrib-spi.sgml

Lines changed: 215 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,215 @@
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/contrib-spi.sgml,v 1.1 2007/12/03 04:18:47 tgl Exp $ -->
2+
3+
<sect1 id="contrib-spi">
4+
<title>spi</title>
5+
6+
<indexterm zone="contrib-spi">
7+
<primary>SPI</primary>
8+
<secondary>examples</secondary>
9+
</indexterm>
10+
11+
<para>
12+
The <filename>contrib/spi</> module provides several workable examples
13+
of using SPI and triggers. While these functions are of some value in
14+
their own right, they are even more useful as examples to modify for
15+
your own purposes. The functions are general enough to be used
16+
with any table, but you have to specify table and field names (as described
17+
below) while creating a trigger.
18+
</para>
19+
20+
<sect2>
21+
<title>refint.c &mdash; functions for implementing referential integrity</title>
22+
23+
<para>
24+
<function>check_primary_key()</> and
25+
<function>check_foreign_key()</> are used to check foreign key constraints.
26+
(This functionality is long since superseded by the built-in foreign
27+
key mechanism, of course, but the module is still useful as an example.)
28+
</para>
29+
30+
<para>
31+
<function>check_primary_key()</> checks the referencing table.
32+
To use, create a BEFORE INSERT OR UPDATE trigger using this
33+
function on a table referencing another table. You are to specify
34+
as trigger arguments: triggered table column names which correspond
35+
to foreign key, referenced table name and column names in referenced
36+
table which correspond to primary/unique key. To handle multiple
37+
foreign keys, create a trigger for each reference.
38+
</para>
39+
40+
<para>
41+
<function>check_foreign_key()</> checks the referenced table.
42+
To use, create a BEFORE DELETE OR UPDATE trigger using this
43+
function on a table referenced by other table(s). You are to specify
44+
as trigger arguments: number of references for which the function has to
45+
perform checking, action if referencing key found ('cascade' &mdash; to delete
46+
corresponding foreign key, 'restrict' &mdash; to abort transaction if foreign keys
47+
exist, 'setnull' &mdash; to set foreign key referencing primary/unique key
48+
being deleted to null), triggered table column names which correspond
49+
to primary/unique key, then referencing table name and column names
50+
corresponding to foreign key (repeated for as many referencing tables/keys
51+
as were specified by first argument). Note that the primary/unique key
52+
columns should be marked NOT NULL and should have a unique index.
53+
</para>
54+
55+
<para>
56+
There are examples in <filename>refint.example</>.
57+
</para>
58+
</sect2>
59+
60+
<sect2>
61+
<title>timetravel.c &mdash; functions for implementing time travel</title>
62+
63+
<para>
64+
Long ago, <productname>PostgreSQL</> had a built-in time travel feature
65+
that kept the insert and delete times for each tuple. This can be
66+
emulated using these functions. To use these functions,
67+
you are to add to a table two columns of <type>abstime</> type to store
68+
the date when a tuple was inserted (start_date) and changed/deleted
69+
(stop_date):
70+
71+
<programlisting>
72+
CREATE TABLE mytab (
73+
... ...
74+
start_date abstime default now(),
75+
stop_date abstime default 'infinity'
76+
... ...
77+
);
78+
</programlisting>
79+
80+
So, tuples being inserted with unspecified start_date/stop_date will get
81+
the current time in start_date and <literal>infinity</> in
82+
stop_date.
83+
</para>
84+
85+
<para>
86+
Tuples with stop_date equal to <literal>infinity</> are <quote>valid
87+
now</quote>: when trigger will be fired for UPDATE/DELETE of a tuple with
88+
stop_date NOT equal to <literal>infinity</> then
89+
this tuple will not be changed/deleted!
90+
</para>
91+
92+
<para>
93+
If stop_date is equal to <literal>infinity</> then on
94+
update only the stop_date in the tuple being updated will be changed (to
95+
current time) and a new tuple with new data (coming from SET ... in UPDATE)
96+
will be inserted. Start_date in this new tuple will be set to current time
97+
and stop_date to <literal>infinity</>.
98+
</para>
99+
100+
<para>
101+
A delete does not actually remove the tuple but only set its stop_date
102+
to current time.
103+
</para>
104+
105+
<para>
106+
To query for tuples <quote>valid now</quote>, include
107+
<literal>stop_date = 'infinity'</> in the query's WHERE condition.
108+
(You might wish to incorporate that in a view.)
109+
</para>
110+
111+
<para>
112+
You can't change start/stop date columns with UPDATE!
113+
Use set_timetravel (below) if you need this.
114+
</para>
115+
116+
<para>
117+
<function>timetravel()</> is the general trigger function that supports
118+
this behavior. Create a BEFORE INSERT OR UPDATE OR DELETE trigger using this
119+
function on each time-traveled table. You are to specify two trigger arguments:
120+
name of start_date column and name of stop_date column in triggered table.
121+
Optionally, you can specify one to three more arguments, which must refer
122+
to columns of type <type>text</>. The trigger will store the name of
123+
the current user into the first of these columns during INSERT, the
124+
second column during UPDATE, and the third during DELETE.
125+
</para>
126+
127+
<para>
128+
<function>set_timetravel()</> allows you to turn time-travel on or off for
129+
a table.
130+
<literal>set_timetravel('mytab', 1)</> will turn TT ON for table mytab.
131+
<literal>set_timetravel('mytab', 0)</> will turn TT OFF for table mytab.
132+
In both cases the old status is reported. While TT is off, you can modify
133+
the start_date and stop_date columns freely.
134+
</para>
135+
136+
<para>
137+
<function>get_timetravel()</> returns the TT state for a table without
138+
changing it.
139+
</para>
140+
141+
<para>
142+
There is an example in <filename>timetravel.example</>.
143+
</para>
144+
</sect2>
145+
146+
<sect2>
147+
<title>autoinc.c &mdash; functions for autoincrementing fields</title>
148+
149+
<para>
150+
<function>autoinc()</> is a trigger that stores the next value of
151+
a sequence into an integer field. This has some overlap with the
152+
built-in <quote>serial column</> feature, but it is not the same:
153+
<function>autoinc()</> will override attempts to substitute a
154+
different field value during inserts, and optionally it can be
155+
used to increment the field during updates, too.
156+
</para>
157+
158+
<para>
159+
To use, create a BEFORE INSERT (or optionally BEFORE INSERT OR UPDATE)
160+
trigger using this function. You are to specify
161+
as trigger arguments: the name of the integer column to be modified,
162+
and the name of the sequence object that will supply values.
163+
(Actually, you can specify any number of pairs of such names, if
164+
you'd like to update more than one autoincrementing column.)
165+
</para>
166+
167+
<para>
168+
There is an example in <filename>autoinc.example</>.
169+
</para>
170+
171+
</sect2>
172+
173+
<sect2>
174+
<title>insert_username.c &mdash; functions for tracking who changed a table</title>
175+
176+
<para>
177+
<function>insert_username()</> is a trigger that stores the current
178+
user's name into a text field. This can be useful for tracking
179+
who last modified a particular row within a table.
180+
</para>
181+
182+
<para>
183+
To use, create a BEFORE INSERT and/or UPDATE
184+
trigger using this function. You are to specify a single trigger
185+
argument: the name of the text column to be modified.
186+
</para>
187+
188+
<para>
189+
There is an example in <filename>insert_username.example</>.
190+
</para>
191+
192+
</sect2>
193+
194+
<sect2>
195+
<title>moddatetime.c &mdash; functions for tracking last modification time</title>
196+
197+
<para>
198+
<function>moddatetime()</> is a trigger that stores the current
199+
time into a <type>timestamp</> field. This can be useful for tracking
200+
the last modification time of a particular row within a table.
201+
</para>
202+
203+
<para>
204+
To use, create a BEFORE UPDATE
205+
trigger using this function. You are to specify a single trigger
206+
argument: the name of the <type>timestamp</> column to be modified.
207+
</para>
208+
209+
<para>
210+
There is an example in <filename>moddatetime.example</>.
211+
</para>
212+
213+
</sect2>
214+
215+
</sect1>

‎doc/src/sgml/contrib.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/contrib.sgml,v 1.6 2007/12/02 22:33:20 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/contrib.sgml,v 1.7 2007/12/03 04:18:47 tgl Exp $ -->
22

33
<appendix id="contrib">
44
<title>Additional Supplied Modules</title>
@@ -103,8 +103,10 @@ psql -d dbname -f <replaceable>SHAREDIR</>/contrib/<replaceable>module</>.sql
103103
&pgstattuple;
104104
&pgtrgm;
105105
&seg;
106+
&contrib-spi;
106107
&sslinfo;
107108
&tablefunc;
109+
&test-parser;
108110
&tsearch2;
109111
&uuid-ossp;
110112
&vacuumlo;

‎doc/src/sgml/filelist.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/filelist.sgml,v 1.55 2007/12/02 22:33:20 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/filelist.sgml,v 1.56 2007/12/03 04:18:47 tgl Exp $ -->
22

33
<!entity history SYSTEM "history.sgml">
44
<!entity info SYSTEM "info.sgml">
@@ -117,8 +117,10 @@
117117
<!entity pgstattuple SYSTEM "pgstattuple.sgml">
118118
<!entity pgtrgm SYSTEM "pgtrgm.sgml">
119119
<!entity seg SYSTEM "seg.sgml">
120+
<!entity contrib-spi SYSTEM "contrib-spi.sgml">
120121
<!entity sslinfo SYSTEM "sslinfo.sgml">
121122
<!entity tablefunc SYSTEM "tablefunc.sgml">
123+
<!entity test-parser SYSTEM "test-parser.sgml">
122124
<!entity tsearch2 SYSTEM "tsearch2.sgml">
123125
<!entity uuid-ossp SYSTEM "uuid-ossp.sgml">
124126
<!entity vacuumlo SYSTEM "vacuumlo.sgml">

‎doc/src/sgml/test-parser.sgml

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,87 @@
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/test-parser.sgml,v 1.1 2007/12/03 04:18:47 tgl Exp $ -->
2+
3+
<sect1 id="test-parser">
4+
<title>test_parser</title>
5+
6+
<indexterm zone="test-parser">
7+
<primary>test_parser</primary>
8+
</indexterm>
9+
10+
<para>
11+
This is an example of a custom parser for full text search.
12+
</para>
13+
14+
<para>
15+
It recognizes space-delimited words and returns just two token types:
16+
17+
<programlisting>
18+
mydb=# SELECT * FROM ts_token_type('testparser');
19+
tokid | alias | description
20+
-------+-------+---------------
21+
3 | word | Word
22+
12 | blank | Space symbols
23+
(2 rows)
24+
</programlisting>
25+
26+
These token numbers have been chosen to be compatible with the default
27+
parser's numbering. This allows us to use its <function>headline()</>
28+
function, thus keeping the example simple.
29+
</para>
30+
31+
<sect2>
32+
<title>Usage</title>
33+
34+
<para>
35+
Running the installation script creates a text search parser
36+
<literal>testparser</>. It has no user-configurable parameters.
37+
</para>
38+
39+
<para>
40+
You can test the parser with, for example,
41+
42+
<programlisting>
43+
mydb=# SELECT * FROM ts_parse('testparser', 'That''s my first own parser');
44+
tokid | token
45+
-------+--------
46+
3 | That's
47+
12 |
48+
3 | my
49+
12 |
50+
3 | first
51+
12 |
52+
3 | own
53+
12 |
54+
3 | parser
55+
</programlisting>
56+
</para>
57+
58+
<para>
59+
Real-world use requires setting up a text search configuration
60+
that uses the parser. For example,
61+
62+
<programlisting>
63+
mydb=# CREATE TEXT SEARCH CONFIGURATION testcfg ( PARSER = testparser );
64+
CREATE TEXT SEARCH CONFIGURATION
65+
66+
mydb=# ALTER TEXT SEARCH CONFIGURATION testcfg
67+
mydb-# ADD MAPPING FOR word WITH english_stem;
68+
ALTER TEXT SEARCH CONFIGURATION
69+
70+
mydb=# SELECT to_tsvector('testcfg', 'That''s my first own parser');
71+
to_tsvector
72+
-------------------------------
73+
'that':1 'first':3 'parser':5
74+
(1 row)
75+
76+
mydb=# SELECT ts_headline('testcfg', 'Supernovae stars are the brightest phenomena in galaxies',
77+
mydb(# to_tsquery('testcfg', 'star'));
78+
ts_headline
79+
-----------------------------------------------------------------
80+
Supernovae &lt;b&gt;stars&lt;/b&gt; are the brightest phenomena in galaxies
81+
(1 row)
82+
</programlisting>
83+
</para>
84+
85+
</sect2>
86+
87+
</sect1>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp