{"id":823,"date":"2022-03-13T05:30:12","date_gmt":"2022-03-12T20:30:12","guid":{"rendered":"http:\/\/www.onepage.co.kr\/wordpress\/?p=823"},"modified":"2022-03-13T06:48:42","modified_gmt":"2022-03-12T21:48:42","slug":"postgresql","status":"publish","type":"post","link":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/2022\/03\/13\/postgresql\/","title":{"rendered":"postgreSQL"},"content":{"rendered":"\n\n\n
\n\n\n\n
<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n
\n

\uc774 \ubb38\uc11c\ub294 \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc758 Index,function,trigger,grant,revoke,Large Object\ub97c \uc608\uc81c\uc640 \ud568\uaed8 \uc124\uba85\ud55c \uae00\uc774\ub2e4.
\n=====================================================================================<\/p>\n

=====================================================================================
\n1.INDEX, SEQUENCE, FUNCTION(1)<\/p>\n

1.1 CREATE INDEX
\nINDEX \ub294 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub0b4\uc758 relation(\ud14c\uc774\ube14)\uc5d0 \ub300\ud55c \uac80\uc0c9\uc758 \uc131\ub2a5\uc744 \ub192\uc5ec\uc900\ub2e4.
\nCREATE\u00a0 [UNIQUE]\u00a0 INDEX\u00a0 index_name
\nON\u00a0 table_name\u00a0 (name_of_attribute);<\/p>\n

CREATE\u00a0 [UNIQUE]\u00a0 INDEX\u00a0 index_name\u00a0 ON\u00a0 table_name
\n[USING\u00a0 acc_name] (column [ops_name] [,…]);<\/p>\n

CREATE\u00a0 [UNIQUE]\u00a0 INDEX\u00a0 index_name\u00a0 ON\u00a0 table_name
\n[USING\u00a0 acc_name] (func_name() ops_name );<\/p>\n

ACCESS METHOD . \ub514\ud3f4\ud2b8\ub294 BTREE \uc774\ub2e4.(BTREE,RTREE,HASH)
\nfunc_name\u00a0 :\u00a0 \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218.
\nops_name\u00a0\u00a0 :\u00a0 operator class (int4_ops,int24_ops,int42_ops)
\nbtree(sid int4_ops) \ub294 btree \ub97c \uc774\uc6a9\ud55c INDEX \uc790\ub8cc\ud615\uc774 4-BYTE \uc815\uc218\uc784.
\n\ub514\ud3f4\ud2b8 operator class \ub294 Field Type \uc774\ub2e4.
\n\ud604\uc7ac btree \ub294 7\uac1c\uae4c\uc9c0\uc758 Multi-Column INDEX\ub97c \uc9c0\uc6d0\ud55c\ub2e4.<\/p>\n

INDEX \uc0dd\uc131 1
\nCREATE\u00a0 INDEX\u00a0 indx1
\nON\u00a0 supplier(sid);
\nsupplier\u00a0 relation(\ud14c\uc774\ube14) \uc758 sname attribute(column) \uc744\u00a0 INDEX \ub85c \uc9c0\uc815<\/p>\n

example 2) INDEX \uc0dd\uc131 2<\/p>\n

CREATE INDEX indx2
\nON supplier USING btree(sid int4_pos);<\/p>\n

example 3) INDEX \uc0dd\uc131 3<\/p>\n

CREATE INDEX indx3
\nON supplier USING btree(sid int8_ops);<\/p>\n

example 4) INDEX \uc0dd\uc131 4<\/p>\n

CREATE INDEX indx4
\nON supplier USING btree(sid, tid);<\/p>\n

example 5)\u00a0 INDEX\u00a0 \uc0ad\uc81c<\/p>\n

DROP INDEX indx1;
\nDROP INDEX indx2;
\nDROP INDEX indx3;
\nDROP INDEX indx4;<\/p>\n

=====================================================================================
\n1.2 CREATE SEQUENCE
\nSEQUENCE \ub294 \uc21c\ucc28\uc801\uc778 \uc22b\uc790 \ubc1c\uc0dd\uae30\uc774\ub2e4.
\nCREATE\u00a0 SEQUENCE\u00a0 seq_name [INCREMENT increment]
\n[MINVALUE\u00a0 minvalue]\u00a0 [MANVALUE\u00a0 maxvalue]
\n[START\u00a0 start]\u00a0 [CACHE\u00a0 cache]\u00a0 [CYCLE]<\/p>\n

INCREMENT : \uc774\uac12\uc774 -1 \uc774\uba74 -1 \ub9cc\ud07c \uac10\uc18c , 3 \uc774\uba74 3\uc529 \uc99d\uac00, \ub514\ud3f4\ud2b8\ub294 1 \uc774\ub2e4.
\nMAXVALUE\u00a0 : optional clause , \uc99d\uac00\ud560\uc218 \uc788\ub294 \ucd5c\uace0\uac12\uc744 \uba85\uc2dc\uc801\uc73c\ub85c \uc9c0\uc815
\nSTART\u00a0\u00a0\u00a0\u00a0 : \uc2dc\uc791\uac12
\nCACHE\u00a0\u00a0\u00a0\u00a0 : sequence \uac12\uc744 \uba3c\uc800 \uba54\ubaa8\ub9ac\uc5d0 \ud560\ub2f9\ud558\uc5ec \ube60\ub978 ACCESS \ub97c \uac00\ub2a5\ucf00 \ud55c\ub2e4.
\nCYCLE\u00a0\u00a0\u00a0\u00a0 : \ucd5c\uace0\uac12\uc73c\ub85c \uc99d\uac00\ub418\uba74 \ub2e4\uc2dc \ucd5c\uc18c\uac12\uc73c\ub85c \uc21c\ud658\ud558\uac8c \ud55c\ub2e4.<\/p>\n

CREATE\u00a0 SEQUENCE\u00a0 seq_name1 START 101;
\nSELECT\u00a0 NEXTVAL(‘seq_name1);<\/p>\n

\uacb0\uacfc<\/p>\n

nextval
\n——-
\n114<\/p>\n

=====================================================================================
\n1.3 CREATE FUNCTION
\nFUNCTION \uc740 \uc0c8\ub85c\uc6b4 \ud568\uc218\ub97c \uc815\uc758\ud55c\ub2e4.
\nCREATE\u00a0 FUNCTION\u00a0 func_name([type[,…]])
\nRETURNS\u00a0 return_type [with (attribute [,…])]
\nAS ‘ definition ‘
\nLANGUAGE ‘language_name’;<\/p>\n

LANGUAGE : sql, pgsql, c \ub4f1\uc774 \uc788\ub2e4.<\/p>\n

CREATE\u00a0 FUNCTION\u00a0 test()\u00a0 RETURNS\u00a0 int4
\nAS ‘ SELECT\u00a0 1 ‘
\nLANGUAGE ‘sql’;<\/p>\n

\uc2e4\ud589
\nSELECT\u00a0 test() AS\u00a0 answer;<\/p>\n

\uacb0\uacfc
\nanswer
\n——
\n1<\/p>\n

AS ‘ \uc640 ‘ \uc0ac\uc774\uc5d0 \ud568\uc218\uc758 \ubcf8\ubb38\uc744 \uae30\uc785\ud558\uba74 \ub41c\ub2e4. \ucc38\uace0\ub85c \ubb38\uc790\uc5f4\uc77c \uacbd\uc6b0,
\n‘seq_test1’ \uc640 \uac19\uc740 \uacbd\uc6b0 \ub2e4\uc74c\ucc98\ub7fc \ud55c\ub2e4.<\/p>\n

CREATE\u00a0 FUNCTION\u00a0 test()\u00a0 RETURNS\u00a0 int4
\nAS\u00a0 ‘ SELECT\u00a0 NEXTVAL(”seq_test1”) ‘
\nLANGUAGE\u00a0 ‘sql’;<\/p>\n

\uc5ec\uae30\uc11c NEXTVAL \uc740 SEQUENCE \uad00\ub828 \ub0b4\uc7a5\ud568\uc218\uc774\ub2e4.<\/p>\n

1.4 \uc608\uc81c
\n\ub2e4\uc74c \uc608\uc81c\uc758 \uc774\ub984\uc740 test.sql \uc785\ub2c8\ub2e4. \ub2e4\uc74c \uc608\uc81c\ub97c \ud654\uc77c\ub85c \ub9cc\ub4e4\uc5b4 \ub2e4\uc74c\ucc98\ub7fc \uc2e4\ud589\ud558\uc2dc\uba74 \ub429\ub2c8\ub2e4.<\/p>\n

\\i \/usr\/local\/src\/test.sql
\n——————————————————————-START !!
\n–drop\u00a0 all\u00a0 object\u00a0 for\u00a0 safe_test
\nDROP\u00a0 SEQUENCE\u00a0 seq_test1;
\nDROP\u00a0 SEQUENCE\u00a0 seq_test2;
\nDROP\u00a0 SEQUENCE\u00a0 seq_test3;
\nDROP\u00a0 INDEX\u00a0\u00a0\u00a0\u00a0 ind_test1;
\nDROP\u00a0 INDEX\u00a0\u00a0\u00a0\u00a0 ind_test2;
\nDROP\u00a0 INDEX\u00a0\u00a0\u00a0\u00a0 ind_test3;
\nDROP\u00a0 TABLE\u00a0\u00a0\u00a0\u00a0 tab_test1;
\nDROP\u00a0 TABLE\u00a0\u00a0\u00a0\u00a0 tab_test2;
\nDROP\u00a0 TABLE\u00a0\u00a0\u00a0\u00a0 tab_test3;
\nDROP\u00a0 FUNCTION\u00a0 func_test();<\/p>\n

–create\u00a0 sequence\u00a0 seq_test1,seq_test2,seq_test3
\nCREATE\u00a0 SEQUENCE\u00a0 seq_test1\u00a0 START\u00a0 101;
\nCREATE\u00a0 SEQUENCE\u00a0 seq_test2\u00a0 START\u00a0\u00a0\u00a0 1;
\nCREATE\u00a0 SEQUENCE\u00a0 seq_test3\u00a0 START\u00a0\u00a0\u00a0 1;<\/p>\n

–create table tab_test1,tab_test2,tab_test3
\nCREATE\u00a0 TABLE\u00a0 tab_test1(
\ntab1_id\u00a0 \u00a0 bigint\u00a0 NOT NULL,
\ntab1_name\u00a0 text,
\ntab1_tel\u00a0\u00a0 text,
\nteb1_memo\u00a0 text
\n);<\/p>\n

CREATE\u00a0 TABLE\u00a0 tab_test2(
\ntab2_id\u00a0 \u00a0 bigint\u00a0 NOT NULL,
\ntab2_name\u00a0 text,
\ntab2_tel\u00a0\u00a0 text,
\nteb2_memo\u00a0 text
\n);<\/p>\n

CREATE\u00a0 TABLE\u00a0 tab_test3(
\ntab3_id\u00a0 \u00a0 bigint DEFAULT\u00a0 nextval(‘seq_test3’) NOT NULL,
\ntab3_name\u00a0 text,
\ntab3_tel\u00a0\u00a0 text,
\ntab3_memo\u00a0 text
\n);<\/p>\n

–craete\u00a0 index
\nCREATE\u00a0 UNIQUE\u00a0 INDEX\u00a0\u00a0 ind_test1\u00a0 ON\u00a0 tab_test1(tab1_id);
\nCREATE\u00a0 UNIQUE\u00a0 INDEX\u00a0\u00a0 ind_test2\u00a0 ON\u00a0 tab_test2(tab2_id);
\nCREATE\u00a0 UNIQUE\u00a0 INDEX\u00a0\u00a0 ind_test3\u00a0 ON\u00a0 tab_test3\u00a0 USING\u00a0 btree(tab3_id\u00a0 int4_ops);<\/p>\n

–FUNCTION\u00a0 func_test()
\nCREATE\u00a0 FUNCTION\u00a0 func_test()\u00a0 RETURNS\u00a0 bigint
\nAS\u00a0 ‘ SELECT NEXTVAL(”seq_test1”) ‘
\nLANGUAGE ‘sql’;<\/p>\n

–transaction 1
\nBEGIN;
\nINSERT\u00a0 INTO\u00a0 tab_test1\u00a0 VALUES (func_test(),’jini1′,’000-0000′,’No_Memo1′);
\nINSERT\u00a0 INTO\u00a0 tab_test2\u00a0 VALUES (nextval(‘seq_test2′),’winob1′,’000-0001′,’No_Memo1’);
\nINSERT\u00a0 INTO\u00a0 tab_test3\u00a0 (tab3_name,tab3_tel,tab3_memo)
\nVALUES (‘nogadax1′,’000-0003′,’No_Memo1′);<\/p>\n

INSERT\u00a0 INTO\u00a0 tab_test1\u00a0 VALUES (func_test(),’jini2′,’100-0000′,’No_Memo2’);
\nINSERT\u00a0 INTO\u00a0 tab_test2\u00a0 VALUES (nextval(‘seq_test2′),’winob2′,’100-0001′,’No_Memo2’);
\nINSERT\u00a0 INTO\u00a0 tab_test3\u00a0 (tab3_name,tab3_tel,tab3_memo)
\nVALUES (‘nogadax2′,’100-0003′,’No_Memo2′);<\/p>\n

INSERT\u00a0 INTO\u00a0 tab_test1\u00a0 VALUES (func_test(),’jini3′,’200-0000′,’No_Memo3’);
\nINSERT\u00a0 INTO\u00a0 tab_test2\u00a0 VALUES (nextval(‘seq_test2′),’winob3′,’200-0001′,’No_Memo3’);
\nINSERT\u00a0 INTO\u00a0 tab_test3\u00a0 (tab3_name,tab3_tel,tab3_memo)
\nVALUES (‘nogadax3′,’200-0003′,’No_Memo3′);<\/p>\n

INSERT\u00a0 INTO\u00a0 tab_test1\u00a0 VALUES (func_test(),’jini4′,’300-0000′,’No_Memo4’);
\nINSERT\u00a0 INTO\u00a0 tab_test2\u00a0 VALUES (nextval(‘seq_test2′),’winob4′,’300-0001′,’No_Memo4’);
\nINSERT\u00a0 INTO\u00a0 tab_test3\u00a0 (tab3_name,tab3_tel,tab3_memo)
\nVALUES (‘nogadax4′,’300-0003′,’No_Memo4′);<\/p>\n

INSERT\u00a0 INTO\u00a0 tab_test1\u00a0 VALUES (func_test(),’jini5′,’400-0000′,’No_Memo5’);
\nINSERT\u00a0 INTO\u00a0 tab_test2\u00a0 VALUES (nextval(‘seq_test2′),’winob5′,’400-0001′,’No_Memo5’);
\nINSERT\u00a0 INTO\u00a0 tab_test3\u00a0 (tab3_name,tab3_tel,tab3_memo)
\nVALUES (‘nogadax5′,’400-0003′,’No_Memo5’);<\/p>\n

END;<\/p>\n

–transaction 2
\nBEGIN;
\nSELECT * FROM tab_test1;
\nSELECT * FROM tab_test2;
\nSELECT * FROM tab_test3;
\nVACUUM VERBOSE ANALYZE tab_test1;
\nVACUUM VERBOSE ANALYZE tab_test2;
\nVACUUM VERBOSE ANALYZE tab_test3;
\nEND;<\/p>\n

——————————————————————-End !!<\/p>\n

=====================================================================================
\n2. GRANT and REVOKE
\n2.1 GRANT
\nGRANT\ub294 user,group \ud639\uc740 \ubaa8\ub4e0 user\ub4e4\uc5d0\uac8c \ud574\ub2f9 \uac1d\uccb4\uc5d0 \ub300\ud55c \uc0ac\uc6a9\uad8c\ud55c\uc744 \uc2b9\uc778\ud55c\ub2e4.
\nREVOKE\ub294 user,group \ud639\uc740 \ubaa8\ub4e0 user\ub85c\ubd80\ud130 \uac1d\uccb4\uc5d0 \ub300\ud55c \uc0ac\uc6a9\uad8c\ud55c\uc744 \ubb34\ud6a8\ud654\ud55c\ub2e4.<\/p>\n

GRANT privilege [,…] ON object [,…]
\nTO { PUBLIC | GROUP group | username}<\/p>\n

privilege
\nSELECT : \ud2b9\uc815 TABLE\/VIEW \uc758 column\uc5d0 \ub300\ud55c access \uc744 \uc2b9\uc778
\nINSERT : \ud2b9\uc815 TABLE\uc758 \ubaa8\ub4e0 column \uc5d0 \ub370\uc774\ud0c0\uc758 \uc0bd\uc785\uc5d0 \ub300\ud55c \uad8c\ud55c \uc2b9\uc778
\nUPDTAE : \ud2b9\uc815 TABLE\uc758 \ubaa8\ub4e0 column \uc758 \uac31\uc2e0\uc5d0 \ub300\ud55c \uad8c\ud55c \uc2b9\uc778
\nDELETE : \ud2b9\uc815 TABLE \uc758 row \uc758 \uc0ad\uc81c\uc5d0 \ub300\ud55c \uad8c\ud55c \uc2b9\uc778
\nRULE : \ud2b9\uc815 TABLE\/VIEW\uc5d0 \ub300\ud55c rule \uc744 \uc815\uc758\ud558\ub294 \uad8c\ud55c\uc5d0 \ub300\ud55c \uc2b9\uc778
\nALL : \ubaa8\ub4e0 \uad8c\ud55c\uc744 \uc2b9\uc778\ud55c\ub2e4.<\/p>\n

object
\naccess \ub97c \uc2b9\uc778\ud558\ub294 \uac1d\uccb4\uc758 \uc774\ub984\uc73c\ub85c\uc11c \ub2e4\uc74c\uacfc \uac19\uc740 \uac1d\uccb4\ub4e4\uc774 \uc788\ub2e4.<\/p>\n

Table
\nSequence
\nView
\nIndex<\/p>\n

PUBLIC
\n\ubaa8\ub4e0 \uc720\uc800\ub97c \uc2b9\uc778<\/p>\n

GROUP group
\n\uc0ac\uc6a9 \uad8c\ud55c\uc744 \ud68d\ub4dd\ud560 group\uc744 \uc9c0\uc815, group \uc744 \uba85\uc2dc\uc801\uc73c\ub85c \uc0dd\uc131\ub418\uc5b4\uc838 \uc788\uc5b4\uc57c \ud568.<\/p>\n

username
\n\uc0ac\uc6a9\uad8c\ud55c\uc744 \ud68d\ub4dd\ud560 \uc0ac\uc6a9\uc790\uba85. PUBLIC \uc740 \ubaa8\ub4e0 \uc720\uc800\uc5d0 \ub300\ud574\uc11c \uc801\uc6a9\ub41c\ub2e4.<\/p>\n

Description
\nGRANT \ub294 \uac1d\uccb4 (object) \ub97c \uc0dd\uc131\ud55c \uc720\uc800\uac00 \ubaa8\ub4e0 \uc720\uc800, \ud639\uc740 \uac1c\uc778 \uc720\uc800, \ud639\uc740 \uadf8\ub8f9\uc5d0 \ub300\ud574
\n\ud574\ub2f9 \uac1d\uccb4\uc758 \uc0ac\uc6a9 \uad8c\ud55c\uc744 \ud5c8\uc6a9\ud558\ub3c4\ub85d \ud55c\ub2e4. \uac1d\uccb4\ub97c \uc0dd\uc131\ud55c \uc720\uc800\uac00 \uc544\ub2cc \ub2e4\ub978 \uc720\uc800\ub4e4\uc740 \uadf8
\n\uac1d\uccb4\uc5d0 \ub300\ud55c \uc0ac\uc6a9\uad8c\ud55c\uc774 \uc5c6\uc5b4\uc11c \uc0ac\uc6a9\ud560 \uc218\uac00 \uc5c6\ub2e4. \ub2e8\uc9c0 \uadf8 \ud574\ub2f9 \uac1d\uccb4\ub97c \uc0dd\uc131\ud55c \uc720\uc800\ub9cc\uc774
\n\uc774\ub97c \ud5c8\uc6a9\ud560 \uc218 \uac00 \uc788\ub294\ub370 \uc774\ub294 GRANT \ub97c \uc0ac\uc6a9\ud568\uc73c\ub85c\uc11c \ub2e4\ub978 \uc720\uc800\ub4e4\uc774 \uc0ac\uc6a9\ud560 \uc218 \uc788\ub3c4\ub85d
\n\ud5c8\uc6a9\ud55c\ub2e4. \uc5b4\ub5a4 \uac1d\uccb4\ub97c \uc0dd\uc131\ud55c \uc720\uc800\ub294 \uc790\ub3d9\uc801\uc73c\ub85c \ubaa8\ub4e0 \uad8c\ud55c\uc744 \uac00\uc9c0\uba70 \uc774 \uad8c\ud55c\ub4e4\uc740 SELECT
\nINSERT, UPDATE, DELETE, RULE \ub4f1\uc774\uba70 \ub610\ud55c \uadf8 \uac1d\uccb4 \uc790\uccb4\ub97c \uc0ad\uc81c\ud560 \uc218 \uc788\ub2e4.<\/p>\n

Notes
\npsql \uc5d0\uc11c “\\z” \ub97c \uc0ac\uc6a9\ud558\uc5ec \uc874\uc7ac\ud558\ub294 \uac1d\uccb4\uc5d0 \ub300\ud55c permission \ub4f1\uc744 \ucc38\uc870\ud560 \uc218 \uc788\ub2e4.<\/p>\n

permission \uc815\ubcf4\uc758 \ud615\uc2dd
\nusername=arwR : \uc720\uc800\uc5d0\uac8c \uc2b9\uc778\ub41c \uc0ac\uc6a9\uad8c\ud55c
\ngroup gname=arwR : GROUP \uc5d0\uac8c \uc2b9\uc778\ub41c \uc0ac\uc6a9\uad8c\ud55c
\n=arwR : \ubaa8\ub4e0 \uc720\uc800\uc5d0\uac8c \uc2b9\uc778\ub41c \uc0ac\uc6a9\uad8c\ud55c<\/p>\n

a : INSERT privilege
\nr : SELECT privilege
\nw : UPDATE\/DELETE privilege
\nR : RULE privilege
\narwR : ALL privilege<\/p>\n

USAGE(\uc0ac\uc6a9\uc608)
\nGRANT INSERT ON imsi_table TO PUBLIC<\/p>\n

GRANT ALL ON imsi_table TO nogadax<\/p>\n

2.2 REVOKE
\n\uc720\uc800,\uadf8\ub8f9, \ud639\uc740 \ubaa8\ub4e0 \uc720\uc800\ub85c\ubd80\ud130 access privilege \ub97c \ucde8\uc18c<\/p>\n

REVOKE privilege [,…]
\nON object [,…]
\nFROM { PUBLIC | GROUP gname | username }<\/p>\n

privilege
\nSELECT ,INSERT ,UPDATE, DELETE, RULE, ALL<\/p>\n

object
\n\uc801\uc6a9\ub420 \uc218 \uc788\ub294 \uac1d\uccb4 : table, view, sequence, index<\/p>\n

group
\nprivilege \ub97c \ucde8\uc18c\ud560 \uadf8\ub8f9\uba85<\/p>\n

username
\nPUBLIC<\/p>\n

Description
\nREVOKE \ub294 \uac1d\uccb4\uc758 \uc0dd\uc131\uc790\uac00 \ubaa8\ub4e0 \uc720\uc800, \uc720\uc800, \uadf8\ub8f9\ub4e4\ub85c\ubd80\ud130 \uc804\uc5d0 \uc2b9\uc778\ud588\ub358 \ud37c\ubbf8\uc158\uc744
\n\ud574\uc81c\ud55c\ub2e4.
\nUSAGE(\uc0ac\uc6a9\uc608)
\nREVOKE INSERT ON imsi_table FROM PUBLIC<\/p>\n

REVOKE ALL ON imsi_table FROM nogadax
\n3. TRIGGER
\n3.1 TRIGGER 1<\/p>\n

CREATE\u00a0 TRIGGER\u00a0 name\u00a0 { BEFORE | AFTER } { event [ OR …]}
\nON\u00a0 table\u00a0 FOR\u00a0 EACH\u00a0 { ROW\u00a0 |\u00a0 STATEMENT }
\nEXECUTE\u00a0 PROCEDURE\u00a0 func_name ( )<\/p>\n

event\u00a0\u00a0\u00a0\u00a0 : INPUT , UPDATE , DELETE \ub4f1\uc774 \uc62c \uc218 \uc788\ub2e4.
\nfunc_name : \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218\uc774\ub2e4. plpgsql \uc744 \uad8c\uc7a5\ud558\uace0 \uc2f6\ub2e4.
\n\ub610\ud55c \uc774 \ud568\uc218\uc5d0\ub294 \uc778\uc790\uac00 \ub4e4\uc5b4\uc62c \uc218 \uc5c6\ub2e4.
\n\ub610\ud55c \uc774 \ud568\uc218\uc758 RETURN TYPE \ub85c\ub294 OPAQUE \uc774\uc5b4\uc57c \ud55c\ub2e4.
\n\uc778\uc218\ub97c \uc704\ud574 TRIGGER \uc758 \uc790\uccb4 \ubcc0\uc218\ub97c \uc0ac\uc6a9\ud558\uc5ec\uc57c \ud55c\ub2e4.
\n(new,old,…)<\/p>\n

TRIGGER \ub97c\u00a0 \ucd0c\uc2a4\ub7fd\uac8c \ud45c\ud604\ud558\uc790\uba74 \ubc29\uc544\uc1e0\ub97c \ub2f9\uae30\uba74 \ucd1d\uc54c\uc774 \ub098\uac00\ub294 \uac83\uc5d0 \ube44\uc720\ud560 \uc218 \uc788\ub2e4.
\nTRIGGER \uc740 TUPLE(ROW or RECORD) \uc5d0 \ub300\ud55c\u00a0 INSERT, UPDATE, DELETE \ub97c \uc0ac\uac74(event) \ub85c
\n\ubcf4\uace0 \uc774 \uc0ac\uac74\uc5d0 \uc815\uc758\ub41c \uc5b4\ub5a4 \ud589\ub3d9\uc73c\ub85c \ubc18\uc751\ud558\ub294 \uac83\uc774\ub2e4.&#65533;<\/p>\n

example
\nCREATE\u00a0\u00a0 TRIGGER\u00a0\u00a0\u00a0 trg_test
\nBEFORE\u00a0\u00a0 DELETE\u00a0\u00a0\u00a0\u00a0 OR\u00a0\u00a0 UPDATE\u00a0 ON\u00a0 supplier\u00a0 FOR\u00a0 EACH\u00a0 ROW
\nEXECUTE\u00a0 PROCEDURE\u00a0 check_sup();<\/p>\n

supplier \ud14c\uc774\ube14\uc5d0 DELETE, UPDATE \uac00 \ubc1c\uc0dd\ud558\uba74 \uc774 \ub3d9\uc791\uc774 \ud589\ud574\uc9c0\uae30 \uc804\uc5d0
\ncheck_sup() \ub97c \uc2e4\ud589\ud558\ub77c. \uc774 TRIGGER \uc758 \uc774\ub984\uc740 trg_test \uc774\ub2e4.
\nplpgsql \ub9db\ubcf4\uae30
\nCREATE\u00a0 FUNCTION\u00a0 func_name() RETURNS type
\nAS ‘
\n[DECLARE\u00a0 declarations]
\nBEGIN
\nstatements
\nEND;
\n‘LANGUAGE ‘plpgsql’;<\/p>\n

example
\nCREATE\u00a0 FUNCTION\u00a0 pgsql_test() RETURNS\u00a0 datetime
\nAS ‘
\nDECLARE\u00a0 curtime\u00a0 datetime;
\nBEGIN
\ncurtime:= ”now”;
\nreturn\u00a0 curtime;
\nEND;
\n‘ LANGUAGE ‘plpgsql’;<\/p>\n

3.2 TRIGGER \uc2e4\ud589\uc744 \uc704\ud55c PL\/pgSQL\ub4f1\ub85d \ubc29\ubc95
\n\ub2e4\uc74c\uc740 \uc2e4\ud589 \uc608\uc81c\uc774\ub2e4. \uc6b0\uc120 \ub2e4\uc74c \uc608\uc81c\ub97c \uc801\uc808\ud788 \ubcf5\uc0ac\ub97c \ud574\uc11c \ud30c\uc77c\ub85c \ub9cc\ub4e0\ub2e4.(\uc608 : trg.sql)
\n\ub2e4\uc74c \uc608\uc81c\ub97c \uc2e4\ud589\uc5d0 \uc55e\uc11c \uba3c\uc800 \ud574\uc57c\ud560 \uc77c\uc774 \uc788\ub294\ub370 \uadf8\uac83\uc740 Procedural Languages \ub97c \ub4f1\ub85d\ud558\ub294 \uac83\uc774\ub2e4.
\n\ub2e4\uc74c\uc608\uc81c\uc5d0\ub294 PL\/pgSQL \uc744 \uc774\uc6a9\ud55c \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\ubbc0\ub85c \uc774\uac83\uc744 \ub4f1\ub85d\ud558\ub294 \uac83\uc740 \ud544\uc218\uc774\ub2e4.<\/p>\n

\ubc29\ubc95\uc740 \ub450\uac00\uc9c0\uac00 \uc788\ub2e4.
\n1. template1 \ub370\uc774\ud0c0 \ubca0\uc774\uc2a4\uc5d0 \ub4f1\ub85d\ud558\ub294 \uac83\uc774\ub2e4. \uc774 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \ub4f1\ub85d\uc774 \ub41c\ud6c4
\ntemplate1 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0\uc11c create database \uba85\ub839\uc73c\ub85c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c \uc0dd\uc131\ud558\uba74 \uc790\ub3d9\uc801\uc73c\ub85c
\n\uc0dd\uc131\ub41c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 PL\/pgSQL\uc774 \ub4f1\ub85d\uc774 \ub418\ubbc0\ub85c \ud3b8\ub9ac\ud558\ub2e4.
\n\ub4f1\ub85d\uc5d0 \uc55e\uc11c \ub2e4\uc74c\uc744 \ud655\uc778\ud558\ub77c.<\/p>\n

postgreSQL\uc758 PATH : \uc5ec\uae30\uc11c\uc758 PATH \ub294 \/usr\/local\/pgsql \uc774\ub2e4. \ub610\ud55c pgsql \ub514\ub809\ud1a0\ub9ac \ubc11\uc758
\nlib \ub514\ub809\ud1a0\ub9ac\uc5d0\uc11c plpgsql.so \ub97c \ud655\uc778\ud558\ub77c. \uc544\ub9c8\ub3c4 \uc774 \ud654\uc77c\uc740 \ub2e4 \uc874\uc7ac\ud560 \uac83\uc774\ub2e4.<\/p>\n

\ub4f1\ub85d \uacfc\uc815<\/p>\n

[postgres@nogadax postgres]# psql\u00a0 template1
\ntemplate1=&gt;
\ntemplate1=&gt; CREATE\u00a0 FUNCTION\u00a0 plpgsql_call_handler()\u00a0 RETURNS\u00a0 OPAQUE\u00a0 AS
\ntemplate1-&gt; ‘\/usr\/local\/pgsql\/lib\/plpgsql.so’\u00a0 LANGUAGE\u00a0 ‘c’;
\ntemplate1=&gt;
\ntemplate1=&gt; CREATE\u00a0 TRUSTED\u00a0 PROCEDURAL\u00a0 LANGUAGE\u00a0 ‘plpgsql’
\ntemplate1-&gt; HANDLER\u00a0 plpgsql_call_handler
\ntemplate1-&gt; LANCOMPILER\u00a0 ‘PL\/pgSQL’;
\ntemplate1=&gt; CREATE\u00a0 DATABASE\u00a0 nogadax;
\ntemplate1=&gt; \\q
\n[postgres@nogadax postgres]#
\n2. \ub2e4\uc74c \ubc29\ubc95\uc740 \uc0dd\uc131\ud55c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub9c8\ub2e4 \ud558\ub098\ud558\ub098 \ub2e4 \ub4f1\ub85d\uc744 \ud558\ub294 \uac83\uc774\ub2e4.<\/p>\n

\ub4f1\ub85d \uacfc\uc815<\/p>\n

[postgres@nogadax postgres]# psql\u00a0 nogadax
\nnogadax=&gt;
\nnogadax=&gt; CREATE\u00a0 FUNCTION\u00a0 plpgsql_call_handler()\u00a0 RETURNS\u00a0 OPAQUE\u00a0 AS
\nnogadax-&gt; ‘\/usr\/local\/pgsql\/lib\/plpgsql.so’\u00a0 LANGUAGE\u00a0 ‘c’;
\nnogadax=&gt;
\nnogadax=&gt; CREATE\u00a0 TRUSTED\u00a0 PROCEDURAL\u00a0 LANGUAGE\u00a0 ‘plpgsql’
\nnogadax-&gt; HANDLER\u00a0 plpgsql_call_handler
\nnogadax-&gt; LANCOMPILER\u00a0 ‘PL\/pgSQL’;
\nnogadax=&gt;
\nnogadax=&gt; \\q
\n[postgres@nogadax postgres]#
\n\uc774\uc81c\ub294 \uc704\uc758 \ub450\uac00\uc9c0 \ubc29\ubc95\uc911 \ud558\ub098\ub97c \uc120\ud0dd\ud558\uc5ec \ub4f1\ub85d\uc744 \ud558\uba74 \ub41c\ub2e4.<\/p>\n

3.3 TRIGGER \uc608\uc81c 1
\n\ub2e4\uc74c\uc740 \uc544\ub798 \uc608\uc81c\ub97c \uc2e4\ud589\ud558\ub294 \ubc29\ubc95\uc774\ub2e4.<\/p>\n

[podtgres@nogadax postgres]$ psql\u00a0 nogadax
\nnogadax=&gt; \\i\u00a0 \/home\/postgres\/trg.sql
\n…..
\n…..<\/p>\n

——————————————————-Cut here!!
\n–coded\u00a0 BY\u00a0 NoGaDa-X\u00a0 2000\/02\/19<\/p>\n

–DROP all Object for safe_test
\nDROP FUNCTION ins_row();
\nDROP TRIGGER trg_test ON test1;
\nDROP TABLE\u00a0 test1;
\nDROP TABLE\u00a0 test2;<\/p>\n

–Create Table
\nCREATE\u00a0 TABLE\u00a0 test1(
\ntab1_id\u00a0\u00a0\u00a0 int4,
\ntab1_name\u00a0 text
\n);<\/p>\n

CREATE\u00a0 TABLE\u00a0 test2(
\ntab2_id\u00a0\u00a0\u00a0 int4,
\ntab2_memo\u00a0 text\u00a0 DEFAULT ‘None’
\n);
\n–Create Function
\nCREATE\u00a0 FUNCTION ins_row() RETURNS OPAQUE
\nAS ‘
\nBEGIN
\nINSERT INTO test2(tab2_id) VALUES(new.tab1_id);
\nRETURN new;
\nEND;
\n‘ LANGUAGE ‘plpgsql’;
\n–Create Trigger
\nCREATE\u00a0 TRIGGER\u00a0 trg_test
\nAFTER\u00a0\u00a0 INSERT\u00a0\u00a0 ON test1 FOR\u00a0 EACH\u00a0 ROW
\nEXECUTE PROCEDURE\u00a0\u00a0 ins_row();
\n–INSERT\u00a0 Transaction
\nBEGIN;
\nINSERT\u00a0 INTO\u00a0 test1 values(1,’nogadax’);
\nINSERT\u00a0 INTO\u00a0 test1 values(2,’winob’);
\nINSERT\u00a0 INTO\u00a0 test1 values(3,’diver708′);
\nINSERT\u00a0 INTO\u00a0 test1 values(4,’jini’);
\nINSERT\u00a0 INTO\u00a0 test1 values(5,’opensys’);
\nINSERT\u00a0 INTO\u00a0 test1 values(6,’Linuz’);
\nEND;<\/p>\n

–SELECT TRACTION
\nBEGIN;
\nSELECT\u00a0 *\u00a0 FROM\u00a0 test1;
\nSELECT\u00a0 *\u00a0 FROM\u00a0 test2;
\nEND;<\/p>\n

———————————————————-End !!<\/p>\n

3.4 TRIGGER 2
\nCREATE TRIGGER
\nCREATE\u00a0 TRIGGER\u00a0 trigger_name\u00a0 { BEFORE | AFTER } { event [OR,…] }
\nON\u00a0 table\u00a0 FOR\u00a0 EACH\u00a0 { ROW | STATEMENT }
\nEXECUTE\u00a0 PROCEDURE\u00a0 funcname ();<\/p>\n

trigger_name : TRIGGER \uc758 \uc774\ub984
\ntable\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : Table \uc758 \uc774\ub984
\nevent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : INSERT , DELETE , UPDATE \ub4e4 \uc911\uc5d0\uc11c \ud558\ub098 \ud639\uc740 \ub450\uc138\uac1c\ub97c
\nTRIGGER \ub97c \uae30\ub3d9\uc2dc\ud0a4\uae30 \uc704\ud55c event \ub85c \ubd04
\n( \uc608 : INSERT OR UPDATE )
\nfunc_name\u00a0\u00a0\u00a0 : user \uac00 \uc81c\uacf5\ud55c \ud568\uc218. \uc774 \ud568\uc218\ub294 \ud2b8\ub9ac\uac70\uac00 \uc0dd\uc131\ub418\uae30 \uc804\uc5d0 \ub9cc\ub4e4\uc5b4\uc838\uc57c \ud55c\ub2e4.
\n\ub610\ud55c, \uc774 \ud568\uc218\uc758 return \ud615\uc740\u00a0 opaque\uc774\uba70 \uc778\uc218\uac00 \uc5c6\uc5b4\uc57c \ud55c\ub2e4.
\n(\uc774 \ubd80\ubd84\uc740 PostgreSQL Programmer’s Guide \uc5d0 \ub098\uc640 \uc788\ub294 \ubd80\ubd84\uc778\ub370 function
\n\uc5d0\uc11c \uc65c \uc778\uc218\uac00 \uc4f0\uc774\uba74 \uc548\ub418\ub294\uc9c0 \uadf8\ub9ac\uace0 opaque \ud615\uc758 \ub9ac\ud134 \uac12\ub9cc \ub418\ub294\uc9c0 \ub97c
\n\uc815\ud655\ud788 \uc124\uba85\ud55c \ubd80\ubd84\uc774 \uc5c6\ub2e4.)
\n“CREATE TRIGGER”\ub85c \uc778\ud574 TRIGGER \uac00 \uc131\uacf5\uc801\uc73c\ub85c \uc0dd\uc131\ub418\uba74 CREATE \ub77c\ub294
\n\uba54\uc2dc\uc9c0\uac00 \ucd9c\ub825\ub41c\ub2e4.<\/p>\n

DESCRIPTION
\nCREATE TRIGGER\uc740 \ud604\uc7ac\uc758 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \uc0c8\ub85c\uc6b4 TRIGGER\uc744 \ub4f1\ub85d\ud560 \uac83\uc774\ub2e4.Trigger
\n\uc740 \ud14c\uc774\ube14(\ub9b4\ub808\uc774\uc158)\uacfc \uc5f0\uacc4\ub418\uc5b4\uc11c \ubbf8\ub9ac \uaddc\uc815\ub41c \ud568\uc218 func_name\uc744 \uc2e4\ud589\ud55c\ub2e4.
\n\ud2b8\ub9ac\uac70\ub294 \ud2b8\ub9ac\uac70\uc758 \uc0dd\uc131\uc2dc\u00a0 BEFORE\ud0a4\ub97c \uc0ac\uc6a9\ud558\uc5ec Tuple(row,record)\uc5d0 \uc5b4\ub5a4 event\uac00
\n\ubc1c\uc0dd\ud558\uae30 \uc804\uc5d0 \uae30\ub3d9\ub418\uc5b4 \uc9c8\uc218 \uc788\ub3c4\ub85d \uaddc\uc815\ub418\uc5b4\uc9c8\uc218 \uc788\uc73c\uba70 \ubc18\ub300\ub85c AFTER\ud0a4\ub97c \uc0ac\uc6a9\ud558
\n\uc5ec event\uac00 \uc644\ub8cc \ud6c4\uc5d0 \uae30\ub3d9\ub418\uac8c \ud560\uc218\ub3c4 \uc788\ub2e4.
\n(\ub2e4\uc74c\uc740 \ubd80\ubd84\uc801\uc73c\ub85c Postgres data changes visibility rule \uc774 \ucc38\uc870\ub418\uc5c8\ub2e4.)
\n\ud2b8\ub9ac\uac70\uac00 BEFORE\uc5d0 \uc758\ud574 event\uc804\uc5d0 \uae30\ub3d9\ub418\uc5b4 \uc9c4\ub2e4\uba74, \ud2b8\ub9ac\uac70\ub294 \ud604\uc7ac\uc758 Tuple\uc5d0 \ub300\ud55c
\nevent\ub97c \uac74\ub108\ub6f0\uac8c \ud55c\ub2e4. \ud2b9\ud788 INSERT\ub098 UPDATE\uc758 event\uc5d0 \ub300\ud574\uc11c\ub294 \uc0bd\uc785\ub418\uc5b4\uc9c8 \ud29c\ud50c
\n\uc758 \ubcc0\ud654\ub97c \uc778\uc9c0\ud560 \uc218 \uc5c6\ub2e4. \uc989, BEFORE\uc131\uaca9\uc758 \ud2b8\ub9ac\uac70\ub294 \ubcc0\uacbd\ub418\uc5b4\uc9c8 \ud29c\ud50c\ub4e4\uc5d0 \ub300\ud574\uc11c
\n“invisible”\ud55c \uc0c1\ud0dc\uc774\ub2e4. \ub2e8\uc9c0, \ucc98\ub9ac\ub418\uc5b4\uc9c8 event \ub9cc \uc778\uc2dd\ud560 \uc218 \uc788\ub2e4.<\/p>\n

\ub610\ud55c, \ud2b8\ub9ac\uac70\uac00 AFTER \ud0a4\uc5d0 \uc758\ud574 event\ud6c4\uc5d0 \uae30\ub3d9\ub418\uc5b4\uc9c0\uba74, \ucd5c\uadfc\uc758 \uc0bd\uc785,UPDATE,\uc0ad\uc81c
\n\ub4f1\uc774 \ud2b8\ub9ac\uac70\uc5d0 “visible” \uc774\ub2e4. \uc989, \ubcc0\uacbd\ub41c \ubd80\ubd84\uc744 \ud2b8\ub9ac\uac70\uac00 \uc778\uc9c0\ud560 \uc218 \uc788\ub2e4.
\nevent\ub294 \ub2e4\uc911\uc758 event \ub97c OR \uc5d0 \uc758\ud574 \uaddc\uc815\ud560 \uc218 \uc788\ub2e4. \ub610\ud55c \ub3d9\uc77c\ud55c \ub9b4\ub808\uc774\uc158\uc5d0 \ub3d9\uc77c
\n\ud55c event \ub97c \uc9c0\uc815\ud558\ub294 \ud558\ub098 \uc774\uc0c1\uc758 \ud2b8\ub9ac\uac70\ub97c \uc815\uc758\ud560 \uc218 \uc788\uc73c\ub098, \uc774\ub294 \ud2b8\ub9ac\uac70\uc758 \uae30\ub3d9
\n\uc21c\uc11c\ub97c \uc608\uce21\ud560 \uc218 \uc5c6\uac8c \ub41c\ub2e4.
\n\ud2b8\ub9ac\uac70\uac00 SQL \ucffc\ub9ac\ub97c \uc2e4\ud589\ud560\ub54c \ub2e4\ub978 \ud2b8\ub9ac\uac70\ub4e4\uc744 \uae30\ub3d9\uc2dc\ud0ac \uc218 \uc788\uc73c\uba70 \uc774\ub97c CASCADE
\n\ud2b8\ub9ac\uac70\ub77c \ud55c\ub2e4. \uc774\ub7ec\ud55c \uce90\uc2a4\uce90\uc774\ub4dc \ud2b8\ub9ac\uac70\uc758 \ub808\ubca8\uc5d0\ub294 \uc81c\ud55c\uc774 \uc5c6\uc73c\ubbc0\ub85c \ud55c\ubc88\uc758 \ud2b8\ub9ac
\n\uac70\ub85c \uc5ec\ub7ec\uac1c\uc758 \ub2e4\uc911\uc758 \ud2b8\ub9ac\uac70\ub97c \uae30\ub3d9\uc2dc\ud0ac \uc218 \uc788\ub2e4.
\n\ub3d9\uc77c\ud55c \ub9b4\ub808\uc774\uc158\uc5d0 \ub300\ud55c INSERT \ud2b8\ub9ac\uac70\uac00 \uc788\ub2e4\uba74 \uc774 \ud2b8\ub9ac\uac70\ub294 \ub2e4\uc2dc \ub3d9\uc77c\ud55c \ub9b4\ub808\uc774\uc158
\n\uc5d0 \ub300\ud55c \ud2b8\ub9ac\uac70\uac00 \uae30\ub3d9\ub418\uc5b4\uc9c8 \uc218 \uc788\ub2e4. \ud558\uc9c0\ub9cc \uc544\uc9c1 PostgreSQL\uc740 \uc774\ub7f0 \ud2b8\ub9ac\uac70\uc5d0 \ub300
\n\ud55c \ud29c\ud50c\uc758 \ub3d9\uae30\ud654\uac00 \uc9c0\uc6d0\ub418\uc9c0 \uc54a\uc73c\ubbc0\ub85c \uc8fc\uc758\ub97c \ud558\uc5ec\uc57c \ud560 \uac83\uc774\ub2e4.<\/p>\n

NOTES
\nCREATE TRIGGER \uc740 PostgreSQL\uc758 \ud655\uc7a5\ub41c \uae30\ub2a5\uc774\ub2e4.
\n\ub2e8\uc9c0 \ub9b4\ub808\uc774\uc158(Table) \uc758 \uc18c\uc720\uc790\ub9cc\uc774 \uadf8 \ub9b4\ub808\uc774\uc158\uc5d0 \ud2b8\ub9ac\uac70\ub97c \uc0dd\uc131\ud560 \uc218 \uc788\ub2e4.
\n\ubc84\uc83c 6.4\uc5d0\uc11c STATEMENT \ub294 \uad6c\ud604\ub418\uc9c0 \uc54a\uc558\ub2e4.<\/p>\n

3.5 TRIGGER \uc608\uc81c 2
\nCASCADING TRIGGER SAMPLE<\/p>\n

—————————————————-Cut here !!
\n–coded\u00a0 by NoGaDa-X
\n–cascading tigger<\/p>\n

DROP TRIGGER tab1_trg ON test1;
\nDROP TRIGGER tab2_trg ON test2;
\nDROP TRIGGER tab3_trg ON test3;<\/p>\n

DROP FUNCTION tab1_func();
\nDROP FUNCTION tab2_func();
\nDROP FUNCTION tab3_func();<\/p>\n

DROP TABLE test1;
\nDROP TABLE test2;
\nDROP TABLE test3;
\nDROP TABLE test4;
\n–Create Table
\nCREATE TABLE test1(
\ntab1_id\u00a0\u00a0 INT4
\n);<\/p>\n

CREATE TABLE test2(
\ntab2_id\u00a0 INT4
\n);<\/p>\n

CREATE TABLE test3(
\ntab3_id\u00a0\u00a0 INT4
\n);<\/p>\n

CREATE TABLE test4(
\ntab4_id\u00a0\u00a0 INT4
\n);
\n–Create Function
\nCREATE FUNCTION tab1_func() RETURNS opaque
\nAS ‘
\nBEGIN
\nINSERT\u00a0 INTO\u00a0 test2 values( new.tab1_id);
\nRETURN new;
\nEND;
\n‘ LANGUAGE ‘plpgsql’;<\/p>\n

CREATE FUNCTION tab2_func() RETURNS opaque
\nAS ‘
\nBEGIN
\nINSERT\u00a0 INTO\u00a0 test3 values( new.tab2_id);
\nRETURN new;
\nEND;
\n‘ LANGUAGE ‘plpgsql’;<\/p>\n

CREATE FUNCTION tab3_func() RETURNS opaque
\nAS ‘
\nBEGIN
\nINSERT\u00a0 INTO\u00a0 test4 values( new.tab3_id);
\nRETURN new;
\nEND;
\n‘ LANGUAGE ‘plpgsql’;
\n–Create Trigger
\nCREATE TRIGGER tab1_trg AFTER
\nINSERT OR UPDATE ON test1 FOR EACH ROW
\nEXECUTE PROCEDURE tab1_func();<\/p>\n

CREATE TRIGGER tab2_trg AFTER
\nINSERT OR UPDATE ON test2 FOR EACH ROW
\nEXECUTE PROCEDURE tab2_func();<\/p>\n

CREATE TRIGGER tab3_trg AFTER
\nINSERT OR UPDATE ON test3 FOR EACH ROW
\nEXECUTE PROCEDURE tab3_func();
\n–transaction
\nBEGIN;
\nINSERT INTO test1 VALUES (1);
\nSELECT * from test1;<\/p>\n

INSERT INTO test1 VALUES (2);
\nSELECT * from test2;<\/p>\n

INSERT INTO test1 VALUES (3);
\nSELECT * from test3;<\/p>\n

INSERT INTO test1 VALUES (4);
\nSELECT * from test4;
\nEND;<\/p>\n

———————————————–End !!<\/p>\n

4. PL\/pgSQL
\n4.1 PL\/pgSQL 1 (PL\/pgSQL\ucc98\ub9ac\uae30 \ub4f1\ub85d)
\n1. Procedure Language
\nPostgres 6.3 \ubc84\uc83c \ubd80\ud130 PostgreSQL\uc740 procedural Language(PL)\ub97c \uc9c0\uc6d0\ud558\uae30 \uc2dc\uc791\ud588\ub2e4.
\n\uc774\uac83\uc740 PostgreSQL\ub9cc\uc758 \ud2b9\ubcc4\ud55c \uacbd\uc6b0\ub85c\uc11c oracle \uc758 PL\/SQL\uacfc \ube44\uc720\ub420 \uc218 \uc788\ub2e4.
\n\ud558\uc9c0\ub9cc \ud2b9\uc218\ud55c \uc5b8\uc5b4\uc778 PostgreSQL\uc758 PL\uc740 PostgreSQL\uc5d0 \ub0b4\uc7a5\ub41c\uac83 \uc544\ub2c8\uace0 \ubaa8\ub4c8\ud654\ub41c Handler\ub97c
\n\ub2e4\uc2dc \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \ub4f1\ub85d\uc744 \ud574\uc8fc\uc5b4\uc57c \ud55c\ub2e4. \uadf8\ub807\uc9c0 \uc54a\uc73c\uba74 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub294 PL \ub85c \uc4f0 \uc5ec\uc9c4
\nfunction\uc758 \ub0b4\uc6a9\uc744 \uc774\ud574\ud560 \uc218 \uc5c6\uc744 \uac83\uc774\ub2e4. \uacb0\ub860\uc801\uc73c\ub85c \ucc98\ub9ac\uae30\ub294 \uacf5\uc720\uac1d\uccb4\ub85c\uc11c \ucef4\ud30c\uc77c\ub418\uba70
\n\ub3d9\uc801\uc73c\ub85c Load \ub418\ub294 \ud2b9\ubcc4\ud55c \uc5b8\uc5b4 \ucc98\ub9ac \uae30\ub2a5\uc774\ub2e4.
\n\uc5ec\uae30\uc11c\ub294 PL Handler\uc758 \ub4f1\ub85d\uc758 \uc608\ub85c\uc11c PL\uc758 \ud55c\uc885\ub958\uc778 PL\/pgSQL \uc5b8\uc5b4\ub97c \ub4f1\ub85d\ud558\uaca0\ub2e4.<\/p>\n

Installing Procedural Languages<\/p>\n

\uacf5\uc720\uac1d\uccb4\uc778 \ucc98\ub9ac\uae30\ub294 \ucef4\ud30c\uc77c\ub41c \ud6c4 \uc778\uc2a4\ud1a8\ub418\uc5b4\uc57c \ud558\ub294\ub370 \ub514\ud3f4\ud2b8\ub85c PL\/pgSQL \uc740 PostgreSQL \uc124\uce58\uc2dc
\n\uc790\ub3d9\uc73c\ub85c \ucef4\ud30c\uc77c\ub41c\ud6c4 \ub77c\uc774\ube0c\ub7ec\ub9ac \ub514\ub809\ud1a0\ub9ac\uc5d0 \ub193\uc5ec\uc9c4\ub2e4. \ub2e4 \ub978 \ucc98\ub9ac\uae30\uc778 PL\/Tcl \uc740 PostgreSQL \ucef4\ud30c\uc77c\uc2dc
\n\uba85\uc2dc\uc801\uc73c\ub85c \uc124\uc815\ub418\uc5b4\uc57c\uc9c0\ub9cc \ucef4\ud30c \uc77c\ub418\uba70 \ub77c\uc774\ube0c\ub7ec\ub9ac \ub514\ub809\ud1a0\ub9ac\uc5d0 \ub193\uc5ec\uc9c4\ub2e4.
\n\ub77c\uc774\ube0c\ub7ec\ub9ac \ub514\ub809\ud1a0\ub9ac\ub294 \uc124\uce58\ub418\uc5b4\uc9c8 PostgreSQL\uc758 \ubc14\ub85c \ubc11\uc758 lib \uc774\ub2e4.
\n\uc608\ub97c \ub4e4\uc5b4 PostgreSQL\uc758 \uc808\ub300\uacbd\ub85c\uac00 \ub2e4\uc74c\uacfc \uac19\ub2e4\uace0 \ud558\uc790.
\n\/usr\/local\/pgsql
\n\uadf8\ub7ec\uba74 \ub77c\uc774\ube0c\ub7ec\ub9ac \ub514\ub809\ud1a0\ub9ac\uc758 \uc808\ub300\uacbd\ub85c\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.
\n\/usr\/local\/pgsql\/lib<\/p>\n

PL\/pgSQL \uc5b8\uc5b4 \ucc98\ub9ac\uae30\ub97c \uc124\uce58\ud558\uae30 \uc704\ud574\uc11c\ub294 \uba3c\uc800 \uc704\uc758 \ub77c\uc774\ube0c\ub7ec\ub9ac \ub514\ub809\ud1a0\ub9ac\uc5d0\uc11c “plpgsql.so” \ub97c
\n\uba3c\uc800 \ud655\uc778\ud558\uc5ec\uc57c \ud55c\ub2e4.<\/p>\n

\ud655\uc778 \ud6c4 CREATE FUNCTION \uc640 CREATE PROCEDURAL LANGUAGE \uc5d0 \uc758\ud574 \uac01 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \uc5d0\uc11c \ub4f1\ub85d\uc744 \ud558\uc5ec\uc57c
\n\ud55c\ub2e4. \uac01 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0\uc11c \ub4f1\ub85d\uc744 \ud558\uc9c0\uc54a\uace0 \uc77c\uad04\uc801\uc73c\ub85c \ucc98\ub9ac\ud558\uace0 \uc2f6\ub2e4\uba74 PostgreSQL \uc758 \ud2b9\ubcc4\ud78c
\n\ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc778 “template1” \uc5d0\uc11c \ub4f1\ub85d\uc744 \ud558\uba74\ub41c\ub2e4. template1 \uc5d0\uc11c \ub4f1\ub85d\uc774 \ub418\uc5c8\ub2e4\uba74 \ucc28\ud6c4 \uc0dd\uc131\ub418\ub294
\n\ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0\ub294 \uc790\ub3d9\uc801\uc73c\ub85c \ucc98\ub9ac\uae30\uac00 \ub4f1\ub85d\ub41c\ub2e4.<\/p>\n

PL\/pgSQL \ucc98\ub9ac\uae30 \ub4f1\ub85d \uc608\uc81c
\n[postgres@nogadax postgresql]psql template1
\ntemplate1=&gt;
\ntemplate1=&gt; CREATE\u00a0 FUNCTION\u00a0 plpgsql_call_handler()\u00a0 RETURNS\u00a0 OPAQUE
\ntemplate1-&gt; AS\u00a0 ‘ \/usr\/local\/pgsql\/lib\/plpgsql.so ‘
\ntemplate1-&gt; LANGUAGE\u00a0 ‘C’ ;
\ntemplate1=&gt;
\ntemplate1=&gt; CREATE\u00a0 TRUSTED\u00a0 PROCEDURAL\u00a0 LANGUAGE\u00a0 ‘plpgsql’
\ntemplate1-&gt; HANDLER\u00a0 plpgsql_call_handler
\ntemplate1-&gt; LANCOMPILER\u00a0 ‘PL\/pgSQL’ ;
\ntemplate1=&gt;
\ntemplate1=&gt; CREATE\u00a0 DATABASE\u00a0 nogadax ;
\ntemplate1=&gt;<\/p>\n

\ud639\uc740 \uc704\uc758 \ubb38\uc744 \ud654\uc77c\ub85c \uc800\uc7a5\ud55c\ud6c4 \\i \ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.<\/p>\n

template1=&gt; \\i\u00a0\u00a0 \/usr\/local\/src\/plsql_inst.sql<\/p>\n

“CREATE TRUSTED PROCEDURAL LANGUAGE” \uc5d0\uc11c TRUSTED \ud0a4\uc6cc\ub4dc\ub294 PostgreSQL \uc758 \uc288\ud37c\uc720\uc800 \uad8c\ud55c\uc774 \uc5c6\ub294
\n\uc77c\ubc18 \uc720\uc800\uac00 “CREATE FUNCTION” \uc774\ub098 “CREATE TRIGGER” \ubb38\uc744 \uc0ac\uc6a9\ud560 \ub54c \ub4f1\ub85d \ub41c procedure
\nlanguage(PL) \ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub3c4\ub85d \ud574\uc900\ub2e4.<\/p>\n

2. PL\/pgSQL
\nPL\/pgSQL \uc740 PostgreSQL \ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \uc2dc\uc2a4\ud15c\uc5d0\uc11c “Loadable Procedural Language”\uc774\ub2e4.
\n\uc774 \ud328\ud0a4\uc9c0\ub294 Jan Wieck \uc5d0\uc758\ud574 \uc791\uc131\ub418\uc5c8\ub2e4.<\/p>\n

OVERVIEW
\n1. PL\/pgSQL \uc740 function \uc774\ub098 trigger procedure \ub97c \ub9cc\ub4dc\ub294\ub370 \uc0ac\uc6a9\ub418\uc5b4 \uc9c8 \uc218 \uc788\ub2e4.
\n2. SQL \ubb38\uc5d0 \uc81c\uc5b4 \uad6c\uc870\ub97c \ucd94\uac00\ud560 \uc218 \uc788\ub2e4.
\n3. \ubcf5\uc7a1\ud55c \uacc4\uc0b0\uc744 \uad6c\ud604\ud560 \uc218 \uc788\ub2e4.
\n4. user\uac00 \uc815\uc758\ud55c Type, Function, Operation\uc744 \uc0c1\uc18d\ud560 \uc218 \uc788\ub2e4.
\n5. Server \uc5d0 \uc758\ud574 Trusted(Authentication \uad00\ub828\uc758 \ub73b)\ub41c\uac83\uc744 \uc815\uc758\ud560 \uc218 \uc788\ub2e4.
\n6. \uc0ac\uc6a9\ud558\uae30 \uc27d\ub2e4.<\/p>\n

\uc124\uba85
\nPL\/pgSQL \uc740 \ub300\uc18c\ubb38\uc790\uc758 \uad6c\ubd84\uc774 \uc5c6\uc73c\ubbc0\ub85c \ud0a4\uc6cc\ub4dc\ub098 Identifier \ub4e4\uc740 \ub300\uc18c\ubb38\uc790
\n\uad6c\ubd84\uc5c6\uc774 \ud63c\uc6a9\ub418\uc5b4 \uc4f0\uc77c \uc218 \uc788\ub2e4.<\/p>\n

PL\/pgSQL \uc740 \ube14\ub7ed \uc9c0\ud5a5\uc5b8\uc5b4\uc774\ub2e4. \ube14\ub7ed\uc740 \ub2e4\uc74c\ucc98\ub7fc \uc815\uc758\ub418\uc5b4\uc9c4\ub2e4.<\/p>\n

[ Label ]
\n[ DECLARE\u00a0 declarations ]
\nBEGIN
\nstatements
\nEND;<\/p>\n

\ube14\ub7ed\uc758 statements\uad6c\uc5ed\ub0b4\uc5d0 \uc5ec\ub7ec\uac1c\uc758 sub-block\uc774 \uc0dd\uc131\ub420 \uc218 \uc788\uc73c\uba70 \uc774\ub294 \uc11c\ube0c\ube14\ub7ed\ub0b4\uc758 \ubcc0\uc218 \ub4e4\uc744
\n\uc11c\ube0c\ube14\ub7ed \uc678\ubd80\ub85c\ubd80\ud130 \uc228\uae30\uae30 \uc704\ud574 \uc0ac\uc6a9\ub418\uc5b4\uc9c8\uc218 \uc788\ub2e4. \ube14\ub7ed \uc55e\ubd80\ubd84\uc758 declarations\uad6c\uc5ed \uc5d0\uc11c \uc120\uc5b8\ub41c
\n\ubcc0\uc218\ub294 function \uc774 \ud638\ucd9c\ub420 \ub54c \ub2e8\uc9c0 \ud55c\ubc88 \ucd08\uae30\ud654\uac00 \ub418\ub294 \uac83\uc774 \uc544\ub2c8\ub77c \ube14\ub7ed\uc73c\ub85c \uc9c4\uc785\ud560 \ub54c\ub9c8\ub2e4 \ub9e4\ubc88
\n\ub514\ud3f4\ud2b8 \uac12\uc73c\ub85c \ucd08\uae30\ud654\ub41c\ub2e4.
\nPL\/pgSQL \uc758 BEGIN\/END \uc640 Transaction(BEGIN; END;)\uc744 \uc704\ud55c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \uba85\ub839\ubb38\uacfc\ub294 \ub2e4\ub974\ub2e4\ub294\uac83\uc744
\n\uc774\ud574\ud574\uc57c \ud55c\ub2e4. \ub610\ud55c Function\uacfc Trigger Procedure \uc5d0\uc11c\ub294 \ud2b8\ub79c\uc7ad\uc158\uc744 \uc2dc\uc791 \ud558\uac70\ub098 commit \uc744 \ud560 \uc218
\n\uc5c6\uace0 Postgres\ub294 \uc911\ucca9\ub41c \ud2b8\ub79c\uc7ad\uc158\uc744 \uac00\uc9c8 \uc218 \uc5c6\ub2e4.<\/p>\n

—\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : \ud55c \ub77c\uc778\uc758 \uc8fc\uc11d\ucc98\ub9ac
\n\/*\u00a0\u00a0 *\/\u00a0 : \ube14\ub7ed \ub2e8\uc704 \uc8fc\uc11d \ucc98\ub9ac<\/p>\n

example
\nCREATE\u00a0 FUNCTION\u00a0 logfunc2(text,text,text)\u00a0 RETURNS\u00a0 datetime
\nAS ‘
\nDECLARE\u00a0 logtxt1\u00a0 ALIAS\u00a0 FOR\u00a0 $1;
\nlogtxt2\u00a0 ALIAS\u00a0 FOR\u00a0 $2;
\nlogtxt2\u00a0 ALIAS\u00a0 FOR\u00a0 $3;
\ncurtime\u00a0 datetime;
\nBEGIN
\ncurtime :=”now”;
\nINSERT\u00a0 INTO\u00a0 logtable\u00a0 VALUES (logtxt1,logtxt2,logtxt3,curtime);
\nRETURN\u00a0 curtime;
\nEND;
\n‘ LANGUAGE ‘plpgsql’;<\/p>\n

\uc124\uba85
\n$1,$2,$3 \uc740 \ud568\uc218\uc758 \uc778\uc790\ub4e4\ub85c\uc11c \ub098\uc5f4\ub41c \uc21c\uc11c\ub85c\uc11c \ucc38\uc870\ub418\uc5b4\uc9c4\ub2e4.
\nDECLARE \uc758 ALIAS FOR \ubcc0\uc218 $1 \uc5d0 \ub300\ud55c \ubcc4\uba85\uc744 \uc124\uc815\ud55c\ub2e4. \uc774\ub85c\uc11c $1 \uc5d0 \ub300\ud55c \uac00\ub3c5\uc131\uc774 \ub192\uc544\uc9c8\uc218 \uc788\ub2e4.
\ncurtime := ”now”; \ub294 \ubcc0\uc218 curtime\uc5d0 \ud604\uc7ac\uc758 \uc2dc\uac01\uac12\uc744(”now”) \ud560\ub2f9\ud55c\ub2e4.”:=” \uc740 \ubcc0\uc218\uc5d0 \uac12\uc744
\n\ud560\ub2f9\ud560\ub54c \uc4f0\uc778\ub2e4. \ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc704\uc758 \ud568\uc218\uc758 \ub9ac\ud134\uac12\uc774 datetime \uc774\ubbc0\ub85c datetime \ud0c0\uc785\uc758 \ubcc0\uc218 curtime
\n\uc744 \ub9ac\ud134\ud558\uac8c \ub41c\ub2e4.<\/p>\n

4.2 PL\/pgSQL 2
\nexample 1
\n\ub2e4\uc74c\uc740 \uc608\uc81c\uc785\ub2c8\ub2e4. \uc801\ub2f9\ud788 \ud654\uc77c\ub85c \ubcf5\uc0ac\ud574\uc11c \uc2e4\ud589\uc744 \ud558\uba74 \ub429\ub2c8\ub2e4.<\/p>\n

DROP FUNCTION test1();
\nDROP TABLE tab1;<\/p>\n

CREATE TABLE tab1 (
\nid\u00a0\u00a0\u00a0 int4,
\nname\u00a0 text
\n);<\/p>\n

CREATE FUNCTION test1() RETURNS int4
\nAS ‘
\nDECLARE
\nvar1\u00a0 tab1.id%TYPE:=1;
\nvar2\u00a0 tab1.name%TYPE;
\nvar3\u00a0 var2%TYPE:=”nogada”;
\nBEGIN
\nINSERT INTO tab1(id,name) VALUES(var1,var3);
\nRETURN\u00a0 var1;
\nEND;
\n‘\u00a0 LANGUAGE ‘plpgsql’;<\/p>\n

SELECT test1();
\nSELECT * FROM tab1;<\/p>\n

\uc124\uba85
\n\uc704\uc758 \uc608\uc81c\ub294 DROP\uba85\ub839\ubb38\uc73c\ub85c\ubd80\ud130 \uc2dc\uc791\ud55c\ub2e4. \ubcc4\ub2e4\ub978 \uc774\uc720\ub294 \uc5c6\uace0 \uc548\uc804\ud55c \ud14c\uc2a4\ud2b8\ub97c \uc704\ud574 \uae30\uc874\uc5d0 \uc788\uc744\uc9c0
\n\ubaa8\ub97c function\uc774\ub098 table \uc744 \uba3c\uc800 \uc0ad\uc81c\ud55c\ub2e4. DROP \uba85\ub839\uc5b4\ub85c \uc778\ud55c \uc5d0\ub7ec\ub294 \ubb34\uc2dc\ud574\ub3c4 \ub41c\ub2e4.
\nFunction\uc758 DECLARE \ubd80\ubd84\uc740 \ubcc0\uc218\uc758 \uc120\uc5b8 \uad6c\uc5ed\uc73c\ub85c \ubcf4\uba74 \ub418\uaca0\ub2e4. var1 , var2,var3\uc740 \ubcc0\uc218\uba85\uc774\ub2e4.
\ntab.id%TYPE \uc740 var1 \uc758 \ubcc0\uc218\ud615\uc73c\ub85c\uc11c tab.id \uc758 \uc18d\uc131\uc744 \ucc38\uc870\ud558\uba70 \uc774\uc18d\uc131\uc758 \ubc14\ub85c \ub4a4\uc758 %TYPE \uc5d0\uc758\ud574
\n\uc9c0\uc815\ub41c\ub2e4. \ub610\ud55c %TYPE \uc740 \uc55e\uc804\uc5d0 \uc120\uc5b8\ub41c \ubcc0\uc218\uc758 \uc790\ub8d4\ud615\uc744 \ucc38\uc870 \ud560 \uc218 \uc788\ub2e4. var3 \ub294 \ubc14\ub85c \uc804\uc5d0 \uc120\uc5b8\ub41c
\nvar2 \uc758 \uc790\ub8cc\ud615\uc744 \ucc38\uc870\ud55c\ub2e4.<\/p>\n

Trigger Procedure
\nPL\/pgSQL \uc740 \ud2b8\ub9ac\uac70 \ud504\ub85c\uc2dc\uc838\ub97c \uc815\uc758\ud558\ub294\ub370 \uc0ac\uc6a9\ub418\uc5b4\uc9c8 \uc218 \uc788\ub294\ub370 CREATE FUNCTION\ubb38\uc744 \uc0ac\uc6a9 \ud558\uc5ec
\n\uc0dd\uc131\ub418\uc5b4\uc9c4\ub2e4.
\n\uc0dd\uc131\ub420 \ud2b8\ub9ac\uac70 \ud504\ub85c\uc2dc\uc838\ub294 \ub300\uccb4\ub85c \uc778\uc790\uac00\uc5c6\uace0 opaque\ud615\uc744 \ub9ac\ud134\ud558\ub294 \ud568\uc218 \ub85c\uc11c \uc0dd\uc131\ub418\uc5b4\uc9c4\ub2e4.<\/p>\n

\ud2b8\ub9ac\uac70 \ud504\ub85c\uc2dc\uc838\ub85c\uc11c \uc0dd\uc131\ub41c \ud568\uc218\uc5d0\ub294 \uc57d\uac04\uc758 \ud2b9\uc218\ud55c \ubcc0\uc218\ub97c \uac00\uc9c0\uba70 \uc774\ub294 \uc790\ub3d9\uc73c\ub85c \uc0dd\uc131\ub418\uc5b4 \uc9c0\uba70
\n\ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

NEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 :\u00a0 ROW \ub808\ubca8 \ud2b8\ub9ac\uac70\uc0c1\uc5d0\uc11c INSERT\/UPDATE \ub85c \uc778\ud574 \uc0c8\ub85c\ub9ac \uc0dd\uc131\ub41c
\nROW \ub97c \uc720\uc9c0\ud558\ub294 \ubcc0\uc218\ub85c\uc11c \ub370\uc774\ud0c0\ud0c0\uc785\uc740 RECORD \uc774\ub2e4. RECORD
\n\ud615\uc740 \ubbf8\ub9ac \uad6c\uc870\ud654\ub418\uc9c0 \uc54a\uc740 ROWTYPE\ub85c\uc11c selection\uc774\ub098 insert
\n,update \uc2dc \uacb0\uacfc\ub85c \uc0dd\uc131\ub41c \ud558\ub098\uc758 row \ub97c \uc720\uc9c0\ud558\ub294 \ud615\uc774\ub2e4.
\nOLD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 :\u00a0 new \uc640 \ub300\uc870\ub418\ub294 \ubcc0\uc218\ub85c\uc11c UPDATE\ub098 DELETE\ud615 \uc73c\ub85c \uc778\ud574 \ubcc0\uacbd
\n\ub418\uae30 \uc804\uc758 ROW\ub97c \uc720\uc9c0\ud558\ub294 \ubcc0\uc218\uc774\ub2e4.
\nTG_NAME\u00a0\u00a0\u00a0 :\u00a0 \ub370\uc774\ud0c0 \ud0c0\uc785\uc740 NAME \uc774\uace0 \uc2e4\uc81c\ub85c \uae30\ub3d9\ub41c \ud2b8\ub9ac\uac70\uc758 \uc774\ub984\uc5d0 \ub300\ud55c
\n\ubcc0\uc218\uc774\ub2e4.
\nTG_WHEN\u00a0\u00a0\u00a0 :\u00a0 text\ud615\uc774\uace0 BEFORE\ub098\u00a0 AFTER\ub97c \uac00\uc9c4\ub2e4.
\nTG_LEVEL\u00a0\u00a0 :\u00a0 text\ud615\uc774\uace0 ROW\ub098 STATEMENT\ub97c \uac00\uc9c4\ub2e4.
\nTG_OP\u00a0\u00a0\u00a0\u00a0\u00a0 :\u00a0 text\ud615\uc774\uace0 INSERT\ub098 UPDATE\ub098 DELETE \ub97c \uac00\uc9c4\ub2e4.
\nTG_RELID\u00a0\u00a0 :\u00a0 oid\ud615\uc774\uace0(Object ID) \ud2b8\ub9ac\uac70\ub97c \uae30\ub3d9\uc2dc\ud0a4\ub294 \ud14c\uc774\ube14\uc758 Object ID
\n\uc774\ub2e4.
\nTG_RELNAME :\u00a0 name\ud615\uc774\uace0 \ud2b8\ub9ac\uac70\ub97c \uae30\ub3d9\uc2dc\ud0a4\ub294 \ud14c\uc774\ube14\uc758 name \uc744 \uac00\uc9c0\ub294 \ubcc0\uc218
\n\uc774\ub2e4.
\nTG_NARGS\u00a0\u00a0 :\u00a0 Integer\ud615\uc774\uace0 \ud2b8\ub9ac\uac70 \ud504\ub85c\uc2dc\uc838\uc5d0 \uc8fc\uc5b4\uc9c0\ub294 \uc778\uc790\uc758 \uac1c\uc218\uc774\ub2e4.
\nTG_ARGV[]\u00a0 :\u00a0 array of text \ud615\uc774\uace0 \ud2b8\ub9ac\uac70 \ud504\ub85c\uc2dc\uc838\uc5d0 \uc8fc\uc5b4\uc9c0\ub294 \uc778\uc790\ub4e4\uc744
\n\uac12\uc73c\ub85c \uac00\uc9c0\ub294 \ud14d\uc2a4\ud2b8 \ubc30\uc5f4\ud615\uc758 \ubcc0\uc218\uc774\ub2e4.<\/p>\n

4.3 \uc608\uc81c
\n\ub2e4\uc74c\uc744 \ud654\uc77c\ub85c \ub9cc\ub4e4\uc5b4 \uc2e4\ud589\ud574\ubcf4\uc138\uc694.<\/p>\n

——————————————————-Cut here !!
\nDROP\u00a0 TRIGGER\u00a0\u00a0 emp_stamp\u00a0 ON\u00a0 emp;
\nDROP\u00a0 FUNCTION\u00a0 emp_stamp() ;
\nDROP\u00a0 TABLE\u00a0 emp;
\nCREATE\u00a0 TABLE\u00a0 emp(
\nempname\u00a0\u00a0\u00a0\u00a0 text,
\nsalary\u00a0\u00a0\u00a0\u00a0\u00a0 int4,
\nlast_date\u00a0\u00a0 datetime,
\nlast_user\u00a0\u00a0 name
\n);
\nCREATE\u00a0 FUNCTION\u00a0 emp_stamp()\u00a0 RETURNS\u00a0 OPAQUE
\nAS ‘
\nBEGIN<\/p>\n

IF\u00a0 NEW.empname\u00a0 ISNULL\u00a0 THEN
\nRAISE\u00a0 EXCEPTION ”empname cannot be NULL value”;
\nEND\u00a0 IF;<\/p>\n

IF\u00a0 NEW.salary\u00a0 ISNULL\u00a0 THEN
\nRAISE EXCEPTION ”% cannot have NULL salary”, NEW.empname ;
\nEND\u00a0 IF;
\nIF\u00a0 NEW.salary\u00a0 &lt;\u00a0 0\u00a0 THEN
\nRAISE\u00a0 NOTICE ”% cannot have a negative salary”, NEW.empname ;
\nEND\u00a0 IF;
\n–NOTICE TEST
\nRAISE NOTICE ”TRIGGER NAME : %”,TG_NAME ;
\nRAISE NOTICE ”TRIGGER LEVEL : %\u00a0\u00a0 TRIGGER OPERATION : %”,TG_LEVEL , TG_OP;<\/p>\n

–EXCEPTION TEST
\nRAISE EXCEPTION ”TRIGGER WHEN : %”,TG_WHEN;
\nRAISE NOTICE ”TRIGGER LEVEL : %\u00a0\u00a0 TRIGGER OPERATION : %”,TG_LEVEL , TG_OP;
\nNEW.last_date := ”now”;
\nNEW.last_user := getpgusername();
\nRETURN\u00a0 NEW;
\nEND;
\n‘ LANGUAGE ‘plpgsql’;
\nCREATE\u00a0 TRIGGER\u00a0 emp_stamp\u00a0 AFTER\u00a0 INSERT\u00a0 OR\u00a0 UPDATE\u00a0 ON\u00a0 emp
\nFOR\u00a0 EACH\u00a0 ROW\u00a0 EXECUTE\u00a0 PROCEDURE\u00a0 emp_stamp();
\nINSERT\u00a0 INTO\u00a0 emp(empname,salary)\u00a0 VALUES(‘nogadax’,20);
\nINSERT\u00a0 INTO\u00a0 emp(empname) VALUES(‘winob’);
\nINSERT\u00a0 INTO\u00a0 emp(salary)\u00a0 VALUES(10);
\nINSERT\u00a0 INTO\u00a0 emp(empname,salary)\u00a0 VALUES(‘diver’,30);
\nINSERT\u00a0 INTO\u00a0 emp(salary)\u00a0 VALUES(-20);
\nSELECT\u00a0 *\u00a0 FROM\u00a0 emp;<\/p>\n

————————————————————————–End !!<\/p>\n

\uc124\uba85
\nRAISE\ub294 \uba54\uc2dc\uc9c0\ub97c \ub358\uc9c0\ub294 \uac83\uc785\ub2c8\ub2e4.
\nEXCEEPTION\uc740 \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc758 DEBUG\ub808\ubca8\ub85c\uc11c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 log \ub97c \ub0a8\uae30\uace0 \ud2b8\ub79c\uc7ad\uc158\uc744 \uc911\uc9c0\ud55c\ub2e4.
\n\ub2e4\ub978 \ud0a4\uc6cc\ub4dc\ub85c NOTICE\uac00 \uc788 \ub294\ub370 \uc774\uac83\uc740 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c \uc791\uc131\ud558\uace0 \uc774\ub97c \ud074\ub77c\uc774\uc5b8\ud2b8\uce21\uc73c\ub85c\ub3c4 \uc804\uc1a1\ud55c\ub2e4.<\/p>\n

RAISE EXCEPTION ”% cannot have NULL salary”, NEW.empname ;<\/p>\n

\uc704\uc758 \ubb38\uc5d0\uc11c ” \uc640 ” \uc0ac\uc774\uc758 % \ub294 NEW.empname \uc758 \uac12\uc744 \ucd9c\ub825\ud55c\ub2e4.
\nCREATE\u00a0 TRIGGER\u00a0 emp_stamp\u00a0 AFTER\u00a0 INSERT\u00a0 OR\u00a0 UPDATE\u00a0 ON\u00a0 emp
\nFOR\u00a0 EACH\u00a0 ROW\u00a0 EXECUTE\u00a0 PROCEDURE\u00a0 emp_stamp();
\nNEW.empname\uc758 NEW\ub294 RECORD\ud0c0\uc785\uc73c\ub85c\uc11c \ud2b8\ub9ac\uac70\uc758 \uc774\ubca4\ud2b8\uc758 \uc6d0\uc778\uc774 \ub418\ub294 emp \uc758 \uad6c\uc870\ub97c \uac00\uc9c0\uba70 NEW \uc758 \uac12\uc740 event\uc758 \uacb0\uacfc\ub85c\uc11c \ucd94\uac00\ub418\uac70\ub098 \ubcc0\uacbd\ub41c \ud589\uc744 \uac00\uc9c4\ub2e4.<\/p>\n

\uc815\ub9ac<\/p>\n

Declaration : \uc120\uc5b8\ubd80
\nname [CONSTANT] type [NOT NULL] [DEFAULT | :=value]
\nexample )
\nDECLARE
\nvar_a\u00a0 int4\u00a0 DEFAULT\u00a0 5;<\/p>\n

–var_a\u00a0 \ubcc0\uc218\uc758\u00a0 DEFAULT\uc758 \uac12\uc740\u00a0 5\uc774\ub2e4.
\nname class%ROWTYPE
\nexample )
\nDECLARE
\nvar_a\u00a0 test_table%ROWTYPE;<\/p>\n

var_a \ubcc0\uc218\ub294 test_table \uc758 \ud14c\uc774\ube14 \uad6c\uc870\ub97c \uac00\uc9c4\ub2e4.<\/p>\n

name RECORD
\nexample )
\nDECLARE
\nvar_a\u00a0 RECORD ;<\/p>\n

\ud2b9\uc815 \ud14c\uc774\ube14\uc758 \uad6c\uc870\ub97c \uac00\uc9c0\uc9c0 \uc54a\uace0 selection \uc758 \uacb0\uacfc\uc5d0
\n\ub300\ud55c \uad6c\uc870\ub97c \uac00\uc9c8 \uc218 \uc788\ub2e4.(NEW,OLD)&#65533;<\/p>\n

name ALIAS FOR $n;
\n$n \uc5d0 \ub300\ud55c \ubcc4\uce6d
\nRENAME oldname TO newname
\noldname\ub97c\u00a0 newname\ub85c \ubc14\uafc8<\/p>\n

Data Type : \uc790\ub8cc\ud615<\/p>\n

Postgres-BaseType : \ud3ec\uc2a4\ud2b8 \uadf8\ub808\uc2a4\uc758 \uae30\ubcf8 \uc790\ub8cc\ud615( int4,integer,text,char,..)<\/p>\n

variable%TYPE<\/p>\n

class.field%TYPE<\/p>\n

Expression<\/p>\n

Select expression<\/p>\n

Statement : \ucc98\ub9ac\ubb38\uc7a5
\nAssignment : \uac12\uc758 \ud560\ub2f9<\/p>\n

identifier :=expression;<\/p>\n

SELECT\u00a0 expressions\u00a0 INTO\u00a0 target\u00a0 FROM …<\/p>\n

PERFORM\u00a0 query\u00a0 : Calling another function<\/p>\n

RETURN\u00a0 expression;<\/p>\n

RAISE\u00a0 [NOTICE | EXCEPTION]\u00a0 ” message % ”,variable
\n\uc81c\uc5b4\ubb38<\/p>\n

IF\u00a0 expression\u00a0 THEN
\nstatements
\n[ELSE\u00a0 statements]
\nEND\u00a0 IF;
\n[label]
\nLOOP\u00a0 statements\u00a0 END\u00a0 LOOP;
\n[label]
\nWHILE\u00a0 expression\u00a0 LOOP\u00a0 statements\u00a0 END\u00a0 LOOP;
\n[label]
\nFOR\u00a0 name\u00a0 IN\u00a0 [REVERSE]\u00a0 expression\u00a0 LOOP\u00a0 statements\u00a0 END\u00a0 LOOP;
\n[label]
\nFOR\u00a0 record | row\u00a0 IN\u00a0 select_clause\u00a0 LOOP\u00a0 statement
\nEND\u00a0 LOOP;
\nEXIT\u00a0 [label]\u00a0 [WHEN\u00a0 expression];<\/p>\n

5. Large Object with Transaction
\n5.1 Large Object\uc640 \uc608\uc81c
\n\ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc5d0\uc11c\ub294 \ud55c \ud29c\ud50c\uc758 \uc0ac\uc774\uc988\uac00 8192 Byte (8k Bytes) \ub85c \uc81c\ud55c\ub418\uc5b4 \uc788\ub2e4. \ud558\ub098\uc758 \ub808\ucf54\ub4dc\uc5d0
\n\ub4e4\uc5b4\uac08 \uc218 \uc788\ub294 \ub370\uc774\ud0c0\uc758 \ucd1d \ud06c\uae30\uac00 \uc81c\ud55c\ub418\uc5b4 \uc788\uc73c\ubbc0\ub85c \uc774\ubbf8\uc9c0\ub098 \uc0ac\uc774\uc988\uac00 8K \ub97c \ub118\ub294 \ubb38\uc11c\ub4e4\uc740 \ub2e4\ub974\uac8c
\n\uc800\uc7a5\ub418\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n

\ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\ub294 Large Object \ub77c\ub294 \uac1c\ub150\uc73c\ub85c \uc774\ub97c \uadf9\ubcf5\ud558\ub824\ud55c\ub2e4. \uacfc\uac70\uc5d0 \ud3ec\uc2a4\ud2b8\uadf8 \ub808\uc2a4\ub294 \uc774\ub7f0 \ud070
\n\uc0ac\uc774\uc988\uc758 \ub370\uc774\ud0c0\ub97c \uc704\ud574 3\uac00\uc9c0\uc758 \uc9c0\uc6d0\uc774 \uc788\uc5c8\uc73c\ub098 \uc0ac\uc6a9\uc790\ub4e4\uc0ac\uc774\uc758 \uc7a6\uc740 \ud63c\ub780\uc73c\ub85c \ud558\ub098\ub9cc\uc744 \uc9c0\uc6d0\ud558\uac8c
\n\ub418\uc5c8\uace0 \uadf8\uac83\uc740 \ub2e8\uc21c\ud788 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc548\uc758 \ub370\uc774\ud0c0\ub85c \uc11c\uc758 Large Object \ub97c \uc9c0\uc6d0\ud55c\ub2e4. \uc774\uac83\uc740 \uc561\uc138\uc2a4\ub97c
\n\ud560\ub54c \ub290\ub9b4\uc218 \uc788\uc9c0\ub9cc \uc5c4\uaca9\ud55c \ub370\uc774\ud0c0 \ubb34\uacb0\uc131\uc744 \uc81c\uacf5\ud55c\ub2e4.<\/p>\n

\ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\ub294 Large Object \ub97c \ucabc\uac1c\uc5b4 \uc774\ub97c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ud29c\ud50c\ub4e4\uc5d0 \uc800\uc7a5\ud55c\ub2e4. B-tree \uc778\ub371\uc2a4\ub294
\n\ub79c\ub364\ud55c resd-write \uc561\uc138\uc2a4\uc5d0 \ub300\ud55c \ube60\ub978 \uac80\uc0c9\uc744 \ubcf4\uc99d\ud55c\ub2e4.<\/p>\n

\ub2e4\uc74c\uc740 \uc608\uc81c\uc774\ub2e4.<\/p>\n

——————————————————————–
\ndrop\u00a0 table\u00a0 image;<\/p>\n

BEGIN\u00a0 WORK;<\/p>\n

SET\u00a0 TRANSACTION\u00a0 ISOLATION\u00a0 LEVEL\u00a0 SERIALIZABLE;<\/p>\n

create table image(
\nname\u00a0\u00a0\u00a0 text,
\nraster\u00a0 oid
\n);<\/p>\n

insert\u00a0 into\u00a0\u00a0\u00a0 image (name , raster)
\nvalues (‘snapshot’ , lo_import(‘\/usr\/local\/src\/snapshot01.gif’) );<\/p>\n

select\u00a0 lo_export (image.raster , ‘\/tmp\/snap.gif’)
\nfrom\u00a0 image\u00a0 where name=’snapshot’;<\/p>\n

COMMIT\u00a0 WORK;
\n———————————————————————–<\/p>\n

Large Object Note
\n\uc704\uc758 \uc608\uc81c\uc5d0\uc11c \uba85\uc2dc\uc801\uc73c\ub85c \ud2b8\ub79c\uc7ad\uc158\ub0b4\uc5d0\uc11c Large Object \uc758 \ucc98\ub9ac\uac00 \uc774\ub8e8\uc5b4\uc9c0\uace0 \uc788\ub2e4.
\n\uc774\ub294 \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4 6.5 \ubc84\uc83c\ub300\uc5d0\uc11c\ubd80\ud130\uc758 Large Object \ucc98\ub9ac\uc5d0 \ub300\ud55c \uc694\uad6c\uc0ac\ud56d\uc73c\ub85c\uc11c 6.5 \uc774\uc804 \ubc84\uc83c\uc758
\n\uc554\uc2dc\uc801\uc778 \ud2b8\ub79c\uc7ad\uc158 \uc694\uad6c\uc0ac\ud56d\uacfc\ub294 \ub2ec\ub9ac \uba85\uc2dc\uc801\uc778 \ud2b8\ub79c\uc7ad\uc158\uc744 \uc694\uad6c\ud55c \ub2e4. \uc774 \uc694\uad6c\uc0ac\ud56d\uc774 \ubb34\uc2dc\ub41c\ub2e4\uba74,
\n\uc989 \uba85\uc2dc\uc801\uc778 \ud2b8\ub79c\uc7ad\uc158\ubb38\uc774 \uc791\uc131\ub418\uc9c0 \uc54a\ub294\ub2e4\uba74 \ube44\uc815 \uc131\uc801\uc778 \uacb0\uacfc\ub97c \ub9cc\ub4e0\ub2e4.<\/p>\n

\uc124\uba85
\nBEGIN\u00a0 WORK;
\n\uc0ac\uc6a9\uc790 \uc815\uc758 \ud2b8\ub79c\uc7ad\uc158 \uc2dc\uc791<\/p>\n

SET\u00a0 TRANSACTION\u00a0 ISOLATION\u00a0 LEVEL\u00a0 SERIALIZABLE;
\n\ud2b8\ub79c\uc7ad\uc158 \ub808\ubca8 \uc911 \uac00\uc7a5 \uac15\ub825\ud55c SERIZABLE \ub808\ubca8\ub85c \uc7ac \uc124\uc815\uc744 \ud568.<\/p>\n

\ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc758 \ud2b8\ub79c\uc7ad\uc158\uc758 \ub514\ud3f4\ud2b8 \ub808\ubca8\uc740 “READ COMMITTED” \ub85c\uc11c \ud2b8\ub79c\uc7ad\uc158 \ub0b4\uc758
\nquery \ub294 \uc774 query \uac00 \uc2e4\ud589\ub418\uae30\uc804\uc5d0 commit \ub41c \ub370\uc774\ud0c0\ub9cc \ub2e4\ub8f0 \uc218 \uc788\ub2e4.<\/p>\n

SERIALIZABLE \ub294 \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc758 \uac00\uc7a5 \uac15\ub825\ud55c \ud2b8\ub79c\uc7ad\uc158 \ub808\ubca8\ub85c\uc11c \ud2b8\ub79c\uc7ad\uc158\ub0b4\uc758
\nquery\ub294 query\uc2dc\uc791\uc804\uc774 \uc544\ub2cc \uadf8 \ud2b8\ub79c\uc7ad\uc158\uc774 \uc2dc\uc791\ub418\uae30\uc804\uc5d0 commit\ub41c \ub370\uc774\ud0c0\ub9cc\uc744
\n\ub2e4\ub8f0\uc218 \uc788\ub2e4.<\/p>\n

OID \ub294 \uac1d\uccb4\uc5d0\ub300\ud55c \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc758 \uc2dc\uc2a4\ud15c \uad00\ub828 \uc2dd\ubcc4\uc790\uc774\ub2e4.<\/p>\n

lo_import(\uc77d\uc5b4\uc62c \ub370\uc774\ud0c0\uc758 PATH); \ub294 \ub370\uc774\ud0c0\ub97c \uc77d\uc5b4\ub4e4\uc774\ub294 Large Object \uad00\ub828
\n\ub0b4\uc7a5 \ud568\uc218\uc774\ub2e4.<\/p>\n

lo_export( OID , \ub370\uc774\ud0c0\uac00 \uc4f0\uc5ec\uc9c8 \uc2dc\uc2a4\ud15c\uc758 PATH); \ub294 \ub370\uc774\ud0c0\ub97c \uc77d\uc5b4\uc11c \uaebc\ub0b4\ub294
\nLarge Object \uad00\ub828 \ub0b4\uc7a5\ud568\uc218\uc774\ub2e4.<\/p>\n

COMMIT WORK; \ub294 \ud2b8\ub79c\uc7ad\uc158\uc758 \uc644\ub8cc\ub97c \uc758\ubbf8\ud55c\ub2e4. \uc774\ub85c \uc778\ud574 \uc2e4\uc9c8\uc801\uc778 \uac31\uc2e0\uc774\ub098 \uc0ad\uc81c\ub4f1\uc774
\n\uc774\ub8e8\uc5b4\uc9c4\ub2e4.<\/p>\n

5.2 TRANSACTION
\n\ud2b8\ub79c\uc7ad\uc158\uc758 \uc131\uaca9(ACID)
\n\uc6d0\uc790\uc131\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : \ud558\ub098\uc758 \ud2b8\ub79c\uc7ad\uc158\uc740 \ub2e4\uc218\uc758 query\ub97c \uc2e4\ud589\ud558\uc9c0\ub9cc \uc774\ub294 \ub2e8\uc9c0 \ud558\ub098\uc758
\n(ATOMIC)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 query \uc778\uc591 \uc2e4\ud589\ub418\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n

\uc77c\uad00\uc131\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : \ud2b8\ub79c\uc7ad\uc158\uc758 \uc218\ud589\uc5d0 \ub300\ud574 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ub370\uc774\ud0c0\ub4e4\uc758 \uc77c\uad00\uc131\uc740
\n(CONSISTENT)\u00a0\u00a0 \uc720\uc9c0\ub418\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n

\ubd84\ub9ac\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : \uac01 \ud2b8\ub79c\uc7ad\uc158\uc740 \ubd84\ub9ac\ub418\uc5b4 \ub2e4\ub978 \ud2b8\ub79c\uc7ad\uc158\uc911\uc5d0 \uac04\uc12d\ud574\uc11c\ub294 \uc548\ub41c\ub2e4.
\n(ISOLATABLE)\u00a0\u00a0 \uc774\ub294 \ubcd1\ub82c (CONCURRENCY) \uc81c\uc5b4\uc758 \uac1c\ub150\uc73c\ub85c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub294 \uba40\ud2f0
\n\uc720\uc800 \ud658\uacbd\uc77c \uc218 \uc788\uc73c\ubbc0\ub85c \uac01 \uc720\uc800\uc758 \ud2b8\ub79c\uc7ad\uc158\uc740 \uc548\uc804\ud558\uac8c \uc774\ub8e8
\n\uc5b4\uc838\uc57c \ud55c\ub2e4.<\/p>\n

\uc601\uad6c\uc131\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : \ud2b8\ub79c\uc7ad\uc158\uc758 \uc218\ud589\ud6c4 commit \ub41c \ub370\uc774\ud0c0\ub4e4\uc740 \uc601\uad6c\uc801\uc73c\ub85c \uc720\uc9c0\ub418\uc5b4\uc57c
\n(DURABLE)\u00a0\u00a0\u00a0\u00a0\u00a0 \ud55c\ub2e4.<\/p>\n

\ud2b8\ub79c\uc7ad\uc158 \uad00\ub828 SQL \uba85\ub839\uc5b4 \uc815\ub9ac
\nBEGIN [WORK | TRANSACTION]
\nBEGIN : \uc0c8\ub85c\uc6b4 \ud2b8\ub79c\uc7ad\uc158\uc774 Chain Mode\ub85c \uc2dc\uc791\ud588\uc74c\uc744 \uc54c\ub9b0\ub2e4.
\nWORK , TRANCTION : Optional Keyword. They have no effect.<\/p>\n

COMMIT [WORK | TRANSACTION]
\n\ud2b8\ub79c\uc7ad\uc158\ud6c4 \ubcc0\uacbd\ub41c \uacb0\uacfc\ub97c \uc800\uc7a5.<\/p>\n

END [WORK | TRANCTION]
\n\ud604\uc7ac \ud2b8\ub79c\uc7ad\uc158\uc744 COMMIT.
\nEND\ub294 \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4 \ud655\uc7a5\uc73c\ub85c\uc11c COMMIT \uc640 \uac19\uc740 \uc758\ubbf8\uc774\ub2e4.<\/p>\n

LOCK [TABLE] name
\nLOCK [TABLE] name IN [ROW | ACCESS] {SHARE | EXCLUSIVE} MODE
\nLOCK [TABLE] name IN SHARE ROW EXCLUSIVE MODE
\n\uba85\uc2dc\uc801\uc73c\ub85c \ud2b8\ub79c\uc7ad\uc158 \ub0b4\uc758 \ud14c\uc774\ube14\uc744 \uc7a0\uae08.<\/p>\n

ROLLBACK [WORK | TRANSACTION]
\n\ud604\uc7ac \ud2b8\ub79c\uc7ad\uc158\uc744 \uc911\uc9c0\ud55c\ub2e4.
\nABORT [WORK | TRANSACTION]
\n\ud604\uc7ac \ud2b8\ub79c\uc7ad\uc158\uc744 \uc911\uc9c0\ud55c\ub2e4. ABORT \ub294 \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4 \ud655\uc7a5\uc73c\ub85c ROLLBACK\uc640
\n\uac19\uc740 \uc758\ubbf8\ub85c\uc11c \uc4f0\uc778\ub2e4.
\nSET TRANSACTION ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE}
\n\ud604\uc7ac \ud2b8\ub79c\uc7ad\uc158\uc5d0 \ub300\ud55c \ubd84\ub9ac \ub808\ubca8\uc744 \uc124\uc815\ud55c\ub2e4.
\n\uc124\uba85
\nINSERT INTO tab VALUES(‘qwe’,’www’,123);<\/p>\n

\uc704\uc758 INSERT\ubb38 \uc774 \uc131\uacf5\uc801\uc73c\ub85c \uc218\ud589\ub418\uc5c8\ub2e4\uba74 commit \ub420\uac83\uc774\ub2e4.
\n\uc544\ub2c8\uba74 RollBack \ub420\uac83\uc774\ub2e4. \ub2e4\uc2dc \ub9d0\ud574,\u00a0 \uc704\uc758 \ubb38\uc774 \uc131\uacf5\ud558\uba74
\n\ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \uadf8\uc5d0\u00a0 \ub530\ub978\u00a0 \ub370\uc774\ud0c0\uac00 \uc800\uc7a5\ub418\uace0 \uadf8\ub807\uc9c0 \uc54a\uace0
\nINSERT \uc758 \uc2e4\ud589\uacb0\uacfc\uac00 ERROR \uc774\uba74 \ub370\uc774\ud0c0\ub294 \uc800\uc7a5\ub418\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n

\uc774\ub97c autocommit \ub77c \ud558\ub294\ub370 \ub610\ud55c \ub2e4\ub978\ub9d0\ub85c unchained mode
\n\ub77c\uace0\ub3c4 \ud55c\ub2e4.<\/p>\n

\ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc5d0\uc11c\uc758 \uc77c\ubc18\uc801\uc778 \uba85\ub839\ub4e4\uc758 \uc2e4\ud589\uc740 unchained mode \uc774\ub2e4.
\n\uadf8\ub9ac\uace0 \uc774\ub97c \uc880 \ub354 \uadf8\uc220\uc801\uc73c\ub85c \uc11c\uc220\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

“\uac01\uac01\uc758 \ubb38\uc7a5(statement)\ub4e4\uc740 \uc554\uc2dc\uc801\uc778 \ud2b8\ub79c\uc7ad\uc158\ub0b4\uc5d0\uc11c \uc2e4\ud589\ub418\uc5b4\uc9c0\uace0
\n\uadf8 \ubb38\uc7a5\uc758 \ub05d\ubd80\ubd84\uc5d0\uc11c commit\uac00 \uc774\ub8e8\uc5b4\uc9c0\ub294\ub370 \uc2e4\ud589\uc774 \uc131\uacf5\uc801\uc774\uba74 commit
\n\uac00 \ud589\ud574\uc9c0\uace0 \ubc18\ub300\ub85c \uc2e4\ud589\uc774 \uc131\uacf5\uc801\uc774\uc9c0 \uc54a\uc73c\uba74 rollback \ub418\uc5b4\uc9c4\ub2e4.”<\/p>\n

\uacb0\uad6d\uc740 \uac1c\ubcc4\uc801\uc778 SQL \ubb38\ub4e4\uc758 \uc2e4\ud589\uc5d0 \uc788\uc5b4 \uc0ac\uc6a9\uc790\ub4e4\uc740 \uc790\uc2e0\ub3c4 \ubaa8\ub974\uac8c
\n\ud2b8\ub79c\uc7ad\uc158\ub0b4\uc5d0\uc11c \uc218\ud589\ud558\uace0 \uc788\uace0 \ub610\ud55c \uadf8 \uacb0\uacfc\ub3c4 \uc790\uc2e0\ub3c4 \ubaa8\ub974\uac8c commit
\n\uc774\uac70\ub098 rollback\uc774 \uc774\ub8e8\uc5b4\uc9c4\ub2e4.
\nBEGIN \uc740 \uba85\uc2dc\uc801\uc73c\ub85c \ud2b8\ub79c\uc7ad\uc158\uc744 \uc2dc\uc791\ud568\uc744 \uc758\ubbf8\ud558\uba70 autocommit \uc774 \ub418\uc9c0
\n\uc54a\ub294\ub2e4(chained mode). \uba85\uc2dc\uc801\uc778 commit \ubb38\uc774 \uc62c\ub54c\uae4c\uc9c0 \uc791\uc5c5\ub4e4\uc758 \uacb0\uacfc\ub4e4\uc774
\n\ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \uc800\uc7a5\ub418\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n

BEGIN \ubb38 \ubc14\ub85c \ub4a4\uc5d0 SET \ubb38\uc744 \uc0ac\uc6a9\ud558\uc5ec \uadf8 \ud2b8\ub79c\uc7ad\uc158\uc758 \ud2b8\ub79c\uc7ad\uc158 \ubd84\ub9ac \ub808\ubca8
\n\uc744 \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4. SET \ubb38\uc758 \uc608\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.
\nBEGIN WORK;
\nSET\u00a0 TRANSACTION\u00a0 ISOLATION\u00a0 LEVEL\u00a0 SERIALIZABLE
\nINSERT INTO tab VALUES(1,2,3);
\nINSERT INTO tab VALUES(3,4,5);
\nCOMMIT WORK;<\/p>\n

\ud2b8\ub79c\uc7ad\uc158 \ubd84\ub9ac \uc815\ucc45\uc740 \uc5ec\ub7ec \uc720\uc800\uc758 \ub3d9\uc2dc\uc131 \uc5d0 \ub300\ud55c \ubcf4\ub2e4 \uac15\ub825\ud55c \uc81c\ud55c\uc774\ub77c \ud560
\n\uc218 \uc788\uaca0\ub2e4. \ud3ec\uc2a4\ud2b8\uadf8\ub808\uc2a4\uc5d0\uc758 \ub514\ud3f4\ud2b8 \ud2b8\ub79c\uc7ad\uc158 \ubd84\ub9ac\ub808\ubca8\uc740 “READ COMMITTED”
\n\uc774\ub2e4. READ COMMITTED \ubcf4\ub2e4 \ub354\uc6b1\ub354 \uc5c4\uaca9\ud55c \ub808\ubca8\uc774 SERIALIZABLE \uc774\ub2e4.<\/p>\n

6. \ucc38\uace0 \ubb38\uc11c\uc640 \uae30\ud0c0
\nhttp:\/\/database.sarang.net<\/a>
\nhttp:\/\/www.postgresql.org<\/a>
\npostgresql \ub3c4\ud050\uba3c\ud2b8<\/p>\n

programmer’s guide
\nuser’s guide<\/p>\n

 <\/p>\n<\/div>\n<\/div>\n