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

Commit716b8e2

Browse files
committed
Updates for 6.5.
1 parentaf657ae commit716b8e2

File tree

8 files changed

+940
-2
lines changed

8 files changed

+940
-2
lines changed

‎contrib/spi/README.MAX

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,109 @@
1+
2+
Here are general trigger functions provided as workable examples
3+
of using SPI and triggers. "General" means that functions may be
4+
used for defining triggers for any tables but you have to specify
5+
table/field names (as described below) while creating a trigger.
6+
7+
1. refint.c - functions for implementing referential integrity.
8+
9+
check_primary_key () is to used for foreign keys of a table.
10+
11+
You are to create trigger (BEFORE INSERT OR UPDATE) using this
12+
function on a table referencing another table. You are to specify
13+
as function arguments: triggered table column names which correspond
14+
to foreign key, referenced table name and column names in referenced
15+
table which correspond to primary/unique key.
16+
You may create as many triggers as you need - one trigger for
17+
one reference.
18+
19+
check_foreign_key () is to used for primary/unique keys of a table.
20+
21+
You are to create trigger (BEFORE DELETE OR UPDATE) using this
22+
function on a table referenced by another table(s). You are to specify
23+
as function arguments: number of references for which function has to
24+
performe checking, action if referencing key found ('cascade' - to delete
25+
corresponding foreign key, 'restrict' - to abort transaction if foreign keys
26+
exist, 'setnull' - to set foreign key referencing primary/unique key
27+
being deleted to null), triggered table column names which correspond
28+
to primary/unique key, referencing table name and column names corresponding
29+
to foreign key (, ... - as many referencing tables/keys as specified
30+
by first argument).
31+
Note, that NOT NULL constraint and unique index have to be defined by
32+
youself.
33+
34+
There are examples in refint.example and regression tests
35+
(sql/triggers.sql).
36+
37+
To CREATE FUNCTIONs use refint.sql (will be made by gmake from
38+
refint.source).
39+
40+
41+
42+
43+
# Excuse me for my bad english. Massimo Lambertini
44+
#
45+
#
46+
# New check foreign key
47+
#
48+
I think that cascade mode is to be considered like that the operation over
49+
main table is to be made also in referenced table .
50+
When i Delete , i must delete from referenced table ,
51+
but when i update , i update referenced table and not delete like unmodified refint.c .
52+
53+
I made a new version of refint.c that when i update it check the type of modified key ( if is a text , char() i
54+
added '') and then create a update query that do the right thing .
55+
56+
For my point of view that policy is helpfull because i do not have in referenced table
57+
loss of information .
58+
59+
60+
In preprocessor subdir i have placed a little utility that from a SQL92 table definition,
61+
it create all trigger for foreign key .
62+
63+
64+
the schema that i use to analyze the problem is this
65+
66+
create table
67+
A
68+
( key int4 not null primary key ,...,
69+
) ;
70+
71+
create table
72+
REFERENCED_B
73+
( key int 4 , ... ,
74+
foreign key ( key ) references A --
75+
);
76+
77+
78+
--
79+
-- Trigger for REFERENCED_B
80+
--
81+
82+
CREATE INDEX I_REFERENCED_B_KEY ON REFERENCED_B ( KEY ) ;
83+
84+
CREATE TRIGGER T_P_REFERENCED_B_A BEFORE INSERT OR UPDATE ON REFERENCED_B FOR EACH ROW
85+
EXECUTE PROCEDURE
86+
check_primary_key('KEY','A','KEY' );
87+
88+
CREATE TRIGGER T_F_D_A_REFERENCED_B BEFORE DELETE ON A FOR EACH ROW
89+
EXECUTE PROCEDURE
90+
check_foreign_key(1,'cascade','KEY','REFERENCED_B ','KEY' );
91+
92+
CREATE TRIGGER T_F_U_A_REFERENCED_B AFTER UPDATE ON A FOR EACH ROW
93+
EXECUTE PROCEDURE
94+
check_foreign_key(1,'cascade','KEY','REFERENCED_B ','KEY' );
95+
96+
-- ********************************
97+
98+
I write TRIGGER T_F_U_A_REFERENCED_B ( AFTER ) and not BEFORE because if i set
99+
BEFORE , when i try to modify ( update ) a key of A , i start a execution of TRIGGER T_P_REFERENCED_B_A
100+
( check_primary_key) before the real modification of key in A , then the execution of ( check_primary_key) return
101+
not ok.
102+
With AFTER Clausole i modify first key of A then a update the value of referenced table REFERENCED_B.
103+
104+
Try also the new_example.sql to view the modified policy.
105+
I wish that my explain of problem is quite clear .
106+
If there is miss understanding ( cause my bad english ) please send email to massimo.lambertini@everex.it
107+
108+
109+

‎doc/bug.template

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@ System Configuration
2727

2828
Operating System (example: Linux 2.0.26 ELF) :
2929

30-
PostgreSQL version (example: PostgreSQL-6.4) : PostgreSQL-6.4
30+
PostgreSQL version (example: PostgreSQL-6.5) : PostgreSQL-6.5
3131

3232
Compiler used (example: gcc 2.8.0):
3333

‎register.txt

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11

22

3-
(1998-09-01)
3+
(1999-05-01)
44
PostgreSQL has a Web site at http://www.postgresql.org/ which carries details
55
on the latest release, upcoming features, and other information to make your
66
work or play with PostgreSQL more productive.
Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,171 @@
1+
#! /usr/local/bin/python
2+
# advanced.py - demo of advanced features of PostGres. Some may not be ANSI.
3+
# inspired from the Postgres tutorial
4+
# adapted to Python 1995 by Pascal Andre
5+
6+
print"__________________________________________________________________"
7+
print"MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIAL"
8+
print
9+
print"This module is designed for being imported from python prompt"
10+
print
11+
print"In order to run the samples included here, first create a connection"
12+
print"using : cnx = advanced.DB(...)"
13+
print"then start the demo with: advanced.demo(cnx)"
14+
print"__________________________________________________________________"
15+
16+
frompgtoolsimport*
17+
frompgimportDB
18+
19+
# inheritance features
20+
definherit_demo(pgcnx):
21+
print"-----------------------------"
22+
print"-- Inheritance:"
23+
print"--a table can inherit from zero or more tables. A query"
24+
print"--can reference either all rows of a table or all rows "
25+
print"--of a table plus all of its descendants."
26+
print"-----------------------------"
27+
print
28+
print"-- For example, the capitals table inherits from cities table."
29+
print"-- (It inherits all data fields from cities.)"
30+
print
31+
print"CREATE TABLE cities ("
32+
print" nametext,"
33+
print" populationfloat8,"
34+
print" altitudeint"
35+
print")"
36+
print
37+
print"CREATE TABLE capitals ("
38+
print" statevarchar(2)"
39+
print") INHERITS (cities)"
40+
pgcnx.query("CREATE TABLE cities ("\
41+
"nametext,"\
42+
"populationfloat8,"\
43+
"altitudeint)")
44+
pgcnx.query("CREATE TABLE capitals ("\
45+
"statevarchar(2)) INHERITS (cities)")
46+
wait_key()
47+
print
48+
print"-- now, let's populate the tables"
49+
print
50+
print"INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)"
51+
print"INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)"
52+
print"INSERT INTO cities VALUES ('Mariposa', 1200, 1953)"
53+
print
54+
print"INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')"
55+
print"INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')"
56+
print
57+
pgcnx.query(
58+
"INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)")
59+
pgcnx.query(
60+
"INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)")
61+
pgcnx.query(
62+
"INSERT INTO cities VALUES ('Mariposa', 1200, 1953)")
63+
pgcnx.query("INSERT INTO capitals"\
64+
" VALUES ('Sacramento', 3.694E+5, 30, 'CA')")
65+
pgcnx.query("INSERT INTO capitals"\
66+
" VALUES ('Madison', 1.913E+5, 845, 'WI')")
67+
print
68+
print"SELECT * FROM cities"
69+
printpgcnx.query("SELECT * FROM cities")
70+
print"SELECT * FROM capitals"
71+
printpgcnx.query("SELECT * FROM capitals")
72+
print
73+
print"-- like before, a regular query references rows of the base"
74+
print"-- table only"
75+
print
76+
print"SELECT name, altitude"
77+
print"FROM cities"
78+
print"WHERE altitude > 500;"
79+
printpgcnx.query("SELECT name, altitude "\
80+
"FROM cities "\
81+
"WHERE altitude > 500")
82+
print
83+
print"-- on the other hand, you can find all cities, including "
84+
print"-- capitals, that are located at an altitude of 500 'ft "
85+
print"-- or higher by:"
86+
print
87+
print"SELECT c.name, c.altitude"
88+
print"FROM cities* c"
89+
print"WHERE c.altitude > 500"
90+
printpgcnx.query("SELECT c.name, c.altitude "\
91+
"FROM cities* c "\
92+
"WHERE c.altitude > 500")
93+
94+
# arrays attributes
95+
defarray_demo(pgcnx):
96+
print"----------------------"
97+
print"-- Arrays:"
98+
print"-- attributes can be arrays of base types or user-defined "
99+
print"-- types"
100+
print"----------------------"
101+
print
102+
print"CREATE TABLE sal_emp ("
103+
print" nametext,"
104+
print" pay_by_quarterint4[],"
105+
print" scheduletext[][]"
106+
print")"
107+
pgcnx.query("CREATE TABLE sal_emp ("\
108+
"nametext,"\
109+
"pay_by_quarterint4[],"\
110+
"scheduletext[][])")
111+
wait_key()
112+
print
113+
print"-- insert instances with array attributes. "
114+
print" Note the use of braces"
115+
print
116+
print"INSERT INTO sal_emp VALUES ("
117+
print" 'Bill',"
118+
print" '{10000,10000,10000,10000}',"
119+
print" '{{\"meeting\",\"lunch\"}, {}}')"
120+
print
121+
print"INSERT INTO sal_emp VALUES ("
122+
print" 'Carol',"
123+
print" '{20000,25000,25000,25000}',"
124+
print" '{{\"talk\",\"consult\"}, {\"meeting\"}}')"
125+
print
126+
pgcnx.query("INSERT INTO sal_emp VALUES ("\
127+
"'Bill', '{10000,10000,10000,10000}',"\
128+
"'{{\"meeting\",\"lunch\"}, {}}')")
129+
pgcnx.query("INSERT INTO sal_emp VALUES ("\
130+
"'Carol', '{20000,25000,25000,25000}',"\
131+
"'{{\"talk\",\"consult\"}, {\"meeting\"}}')")
132+
wait_key()
133+
print
134+
print"----------------------"
135+
print"-- queries on array attributes"
136+
print"----------------------"
137+
print
138+
print"SELECT name FROM sal_emp WHERE"
139+
print" sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]"
140+
print
141+
printpgcnx.query("SELECT name FROM sal_emp WHERE "\
142+
"sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]")
143+
print
144+
print"-- retrieve third quarter pay of all employees"
145+
print
146+
print"SELECT sal_emp.pay_by_quarter[3] FROM sal_emp"
147+
print
148+
printpgcnx.query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp")
149+
print
150+
print"-- select subarrays"
151+
print
152+
print"SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE"
153+
print" sal_emp.name = 'Bill'"
154+
printpgcnx.query("SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " \
155+
"sal_emp.name = 'Bill'")
156+
157+
# base cleanup
158+
defdemo_cleanup(pgcnx):
159+
print"-- clean up (you must remove the children first)"
160+
print"DROP TABLE sal_emp"
161+
print"DROP TABLE capitals"
162+
print"DROP TABLE cities;"
163+
pgcnx.query("DROP TABLE sal_emp")
164+
pgcnx.query("DROP TABLE capitals")
165+
pgcnx.query("DROP TABLE cities")
166+
167+
# main demo function
168+
defdemo(pgcnx):
169+
inherit_demo(pgcnx)
170+
array_demo(pgcnx)
171+
demo_cleanup(pgcnx)

0 commit comments

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