$B!!%H%j%,!J(BTRIGGER$B!K$O!"%G!<%?%Y!<%9$K99?7!J(Binsert/update/delete$B!K$,$"$C$?;~!"$"$i$+$8$a;XDj$7$?4X?t$r8F$S=P$95!G=$G$9!#(B $B0l$D$N%l%3!<%I$KBP$9$k99?7$K$h$C$F!"B>$N%F!<%V%k$KBP$9$k99?7$dF1$8%F!<%V%k$NJL$N%l%3!<%I$KBP$9$k99?7$,I,MW$K$J$k>l9gEy$K!"$=$l$r<+F0E*$K9T$J$&$?$a$K;H$o$l$^$9!#(B PostgreSQL$B$G$O!"(BC$B8@8l$^$?$O(BPL/pgSQL$B$GDj5A$7$?%f!<%6Dj5A4X?t$r!"%H%j%,$G;XDj$9$k$3$H$,$G$-$^$9!#(B PL/pgSQL$B$GDj5A$7$?%H%j%,MQ$N4X?t$N(Bcreate$BJ8$H!"%H%j%,$N(Bcreate$BJ8$r!"2<$K<($7$^$9!#(B
reate function set_koushin_datetime() returns opaque as '
begin
new.koushin_datetime := ''now'';
return new;
end;
' language 'plpgsql';
create trigger access_upd before update on access_kanri for each row
execute procedure set_koushin_datetime();
|
$B!!$^$:%H%j%,MQ$N4X?t(Bset_koushin_datetime$B$rDj5A$7!"$=$N4X?t$r(Baccess_kanri$B%F!<%V%k$KBP$9$k(Bupdate$B$,9T$o$l$kD>A0$K
$B!!%H%j%,MQ$N4X?t$NJV5QCM$O!"I,$:(Bopaque$B$K$7$^$9!#(B
$B!!>e$GDj5A$7$?4X?t(Bset_koushin_datetime()$B$r!"(Baccess_kanri$B%F!<%V%k$K(Bupdate$B$,
$B!!%7!<%1%s%9!J(BSEQUENCE$B!K$O!"(BPostgreSQL$B$G!"<+F0E*$KO"HV$r@8@.$9$k;EAH$_$G$9!#(B
$B$G$9!#(B
increment$B0J2<$N;XDj$O!"%*%W%7%g%s$G$"$j!"2?$b;XDj$7$J$1$l$P!":G>.CM(B1$B$+$i:GBgCM(B2,147,483,647$B$^$G!"(B1$B$:$DA}2C$9$k%7!<%1%s%9$,:n@.$5$l$^$9!#(B
$B!!%7!<%1%s%9$N5!G=$rMxMQ$7$?$b$N$K!"(Bserial$B7?$H$$$&%G!<%?7?$,$"$j$^$9!#(B
serial$B7?$O!"<+F0E*$KO"HV$r@8@.$9$k!"(BPostgreSQL$BFH<+$N%G!<%?7?$G!"C1FH$G0l0U$K$7$?$$%+%i%`$rDj5A$9$k$N$KJXMx$G$9!#(B
$B$H$$$&$h$&$K;H$$$^$9!#(B
$B!!(Bserial$B$r;H$C$F$$$kI=$r(Bcopy$B$7$F:n$C$?>l9g!"(Bsequence$B$K:GBgCM$r@_Dj$7D>$9I,MW$,$"$j$^$9!#(B
$B$HDj5A$7$?%F!<%V%k$K(Bcopy$B$G%G!<%?$r@_Dj$7$?>l9g!"(Bcopy$B$N8e$G!"(Bmeibo_id_seq$B$H$$$&%7!<%1%s%9$KCM$r@_Dj$7D>$9I,MW$,$"$j$^$9!#6qBNE*$K$O!"%5%V%/%(%j!<$r;H$C$F!"(B
$B$H$$$&$h$&$K!"@_Dj$7$^$9!#(B
$B!!Nc$($P!"(Btable1$B$K!"(Bdate1$B$H$$$&F|IU$rI=8=$9$k%G!<%?7?$N%+%i%`$,$"$k$H$9$k$H!"$3$s$J46$8$N#S#Q#LJ8$G!"F|IU%G!<%?$r!"#1G/8e$NF|IU$G99?7$9$k$3$H$,$G$-$^$9!#(B
$B!!#1G/A0$NF|IU$K$9$k$K$O(B'-'$B$r;H$$$^$9!##1F|J,$:$i$9$K$O!"(Byear$B$NBe$j$K(Bday$B$r;H$$$^$9!#!J(B@$B$O$J$/$F$bNI$$$h$&$G$9!#!K(B
$B!!$3$l$O!"0J2<$N$h$&$K$9$k$H!"$&$^$/$$$-$^$9!#!J
$B!!(Binterval$B$H$$$&$N$O!"K\Mh!"F|IU!&;~9o$N:9$r07$&%G!<%?7?$G$9!#(B
$B$N$h$&$K!"(Binterval$B7?$N%+%i%`$r;}$DI=$rDj5A$9$k$3$H$,$G$-$^$9!#(B
$B!!>e$N$h$&$K%l%3!<%I$rDI2C$9$k$H!"
$B!!$3$l$O!"#4#6#7F|#1#4;~4V#4#6J,#5ICA0$r0UL#$7$^$9!#(B
$B$H$$$&$h$&$K!"%(%i!<$K$J$j$^$7$?!#(B
$B!!(Bselect$BJ8$N0lHLE*$JMQES$O!"%G!<%?%Y!<%9$NI=$+$i%l%3!<%I$r8!:w$9$k$3$H$G$9!#(B
$B$G!"(Btable1$B$K3JG<$5$l$F$$$k%l%3!<%I$NFb!"8!:w>r7o$K9gCW$9$k%l%3!<%I$NA4$F$N%+%i%`$NCM$r
$B$N$h$&$K;XDj$9$k$H!"8!:w$K9gCW$7$?%l%3!<%I$N;XDj$5$l$?%+%i%`$NCM$r#2G\$K$7$?CM$,F@$i$l$^$9!#(B
$B$H;XDj$9$k$H!"8!:w$K9gCW$7$?%l%3!<%I7o?t$rF@$k;v$,$G$-$^$9!#(B
$B!!0J>e$O!"%G!<%?%Y!<%9$r8!:w$7$?7k2L$d!"$=$N7k2L$K4p$E$$$?7W;;$r9T$J$C$?7k2L$rF@$k$b$N$G$7$?!#$3$N$h$&$K!"%G!<%?%Y!<%9$N8!:w$r9T$J$&!"$H$$$&$N$,!"(Bselect$BJ8$N4pK\E*$J5!G=$G$"$k$3$H$O!"4V0c$$$"$j$^$;$s!#$H$3$m$,!"%G!<%?%Y!<%9$N8!:w$r9T$J$o$:$K!"(Bselect$BJ8$r;H$&$3$H$,$"$j$^$9!#(B
$B$H$9$k$7$+$J$$$h$&$G$9!#(B
$B!!$^$?!"4X?t$NJV5QCM$r3NG'$9$k$?$a$K!"(Bselect$B$G4X?t$rF0$+$7$F$_$k!"$H$$$&$3$H$b!"(Bpsql$B%b!<%I$G$O$h$/9T$J$o$l$^$9!#(B
create function$BJ8$N@bL@(B
$B!!(Bbegin$B$+$i(Bend;$B$N4V$K!"create trigger$BJ8$N@bL@(B
$B!!4X?t(Bset_koushin_datetime()$B$O!"%l%3!<%I$N(Bkoushin_datetime$B$K8=:_$NF|IU;~9o$r@_Dj$9$k$b$N$G$9!#(B
koushin_datetime$B$H$$$&(Bdatetime$B7?$^$?$O(Btimestamp$B7?$N%+%i%`$r;}$D%F!<%V%k$G$"$l$P!"$I$s$J%F!<%V%k$KBP$7$F$bE,MQ$9$k$3$H$,$G$-$^$9!#(B
$B!!$3$N4X?t$r(Bbefore update$B$N%H%j%,$H$7$F@_Dj$9$l$P!"%l%3!<%I$N:G=*99?7F|;~$,(Bkoushin_datetime$B$K@_Dj$5$l$k$h$&$K$J$j$^$9!#(B
SEQUENCE$B$N;H$$J}(B
$B!!%7!<%1%s%9$r;H$&$K$O!"$^$:(Bcreate sequnence$BJ8$G!"%7!<%1%s%9$r:n@.$7$F$*$/I,MW$,$"$j$^$9!#(B
create sequnence$BJ8$N9=J8$O!"(B
create sequence $B%7!<%1%s%9L>(B
[ increment $BA}J,CM(B ]
[ minvalue $B:G>.CM(B ]
[ maxvalue $B:GBgCM(B ]
[ start $B=i4|CM(B ]
[ cycle ]
$B!!=i4|CM$O!"(Bstart$B$G;XDj$5$l$J$1$l$P!":G>.CM$K$J$j$^$9!#(B
$B!JA}J,CM$,%W%i%9$N>l9g!#A}J,CM$,%^%$%J%9$N;~$O:GBgCM$,=i4|CM$K$J$k$H$N$3$H$G$9$,!"L$3NG'$G$9!K(B
$B!!(Bcycle$B$O!"CM$r%5%$%/%j%C%/$K;H$&$+$I$&$+$N;XDj$G$9!#$3$l$,;XDj$5$l$?>l9g!":GBgCM$KC#$9$k$H!".CM$K$J$j$^$9!#(B
$B!!(BPostgreSQL$B$G$O!"%7!<%1%s%9$r;H$&$?$a$K!"0J2<$N;0$D$N4X?t$,MQ0U$5$l$F$$$^$9!#(B
$B!!
$B!!8=:_$N%7!<%1%s%9CM$r
$B!!%7!<%1%s%9$NCM$r;XDj$7$?CM$G%j%;%C%H$7$^$9!#(B
$B!!Nc$($P!"(B
SQL# create table table1(id serial, name text);
$B!!>e5-$N(Bcreate table$BJ8$rserial$B$r;H$C$F$$$kI=$r(Bcopy$B$7$?$H$-$NCm0U(B
$B!!I=$G(Bserial$B7?$N%+%i%`$r;H$&$H!"%7!<%1%s%9$,<+F0E*$K:n@.$5$l$^$9!#(B
$B$=$NI=$K%l%3!<%I$rDI2C$9$k$?$S$K!"%7!<%1%s%9$NCM$,#1$:$DA}2C$5$l!"$=$NCM$,(Bserial$B7?$N%+%i%`$K@_Dj$5$l$^$9!#(B
$B!!$3$N$h$&$JI=$r!"(Bcopy$B$7$F!"JL$NI=$K%G!<%?$r0\$7$?>l9g!"$=$N$^$^?7$7$$I=$K$5$i$K%l%3!<%I$rDI2C$7$F$$$C$F$O!"$$$1$^$;$s!#(B
serial$B7?$N%+%i%`$K(Bunique$B@)Ls$d
$B!!Nc$($P!"(B
create table meibo(id serial primary key,name text);
SQL# select setval('meibo_id_seq',(select max(id) from meibo));
interval $B$N;H$$J}(B
SQL# update table1 set date1 = date1 + interval '@ 1 year';
$B!!%7!<%i%+%s%9K\$N(Bp.97$B!"!V?^(B3.4.1 interval$B$r;H$C$?F|IU$N7W;;Nc!W$G$O!"(Bi $B$,(Bdatetime$B7?$G!"#1F|8e$NF|IU$,F@$i$l$k$h$&$K=q$$$F$"$j$^$9$,!"
SQL# select i + '1 day' from t2;
ERROR: Unable to identify an operator '+' for types 'date' and 'unknown'
You will have to retype this query using an explicit cast
SQL# select i + interval '1 day' from t2;
$B!!Nc$($P!"(B
SQL# create table hon (hhh interval);
SQL# insert into hon values(timestamp '1999/12/31' - 'now');
SQL# select * from hon;
hhh
------------------
467 14:46:05 ago
(1 row)
$B!!%7!<%i%+%s%9K\$G!"(Binterval$B$NF~NO7A<0$NNc$H$7$F!"(B10 years$B!"(B3 months$B!"(B1 day$B$H$$$&$h$&$K!"CM$,#1$+$I$&$+$K$h$C$F!"C10L$rI=$9C18l$bC1?t7A!?J#?t7A$,;H$$J,$1$i$o$l$F$$$^$9!#87L)$J;H$$J,$1$,I,MW$J$N$+$I$&$+!"(Byears$B$rNc$K$7$F!"
ERROR: Bad interval external representation '1 yeara'
select$BJ8$NMQES(B
SQL# select * from table1 where $B8!:w>r7o(B;
SQL# select $B%+%i%`L>(B * 2 from table1 where $B8!:w>r7o(B;
$B!!$^$?!"%?!<%2%C%H%j%9%H$K4X?t$r;H$C$?<0$r=q$/$3$H$b$G$-$^$9!#Nc$($P!"(B
SQL# select count(*) from table1 where $B8!:w>r7o(B;
$B!!%?!<%2%C%H%j%9%H$KNI$/;H$o$l$k4X?t$K$O!"(Bmax($B%+%i%`L>(B)$B!"(Bavg($B%+%i%`L>(B)$B!"(Bsum($B%+%i%`L>(B)$BEy$,$"$j$^$9!#$=$l$>$l>r7o$K9gCW$7$?%l%3!<%I$N%+%i%`$N:GBgCM!"%+%i%`$NJ?6QCM!"%+%i%`$N9g7WCM$,F@$k$N$K;H$o$l$^$9!#(B
$B!!Nc$($P!"(Bsetval()$B$H$$$&4X?t$r;H$&$h$&$J>l9g$G$9!#(Bsetval()$B$O!"%7!<%1%s%9$NCM$r;XDj$7$?CM$G%j%;%C%H$9$k4X?t$G$9!#$3$N$h$&$J4X?t$r(BSQL$B$G
SQL# select setval('$B%7!<%1%s%9L>(B',$BCM(B);
$B!!(Bselect$BJ8$K!"$3$N$h$&$J;H$$J}$b$"$k$3$H$rM}2r$7$F$*$/$H!"Lr$KN)$D$3$H$,$"$k$H;W$$$^$9!#(B