このガイドで使用されている例について¶
このガイドの例は、以下に示すタイプと背景で示されています。
例と例からの出力は、白い背景に固定幅の黒いフォントで表示されます。
例では、AdvancedServerのインストール時に作成およびロードされるサンプルテーブル dept, emp および jobhist を使用します。
サンプルデータベースのテーブルとプログラムは、次のスクリプトを実行することでいつでも再作成できます。
/usr/edb/asxx/share/pg-sample.sql
ここで、 xx はAdvancedServerのバージョン番号です。
さらに、Oracleデータベースと互換性のある構文を使用して作成されたデータベースオブジェクトを含むスクリプトが同じディレクトリにあります。このスクリプトファイルは edb-sample.sql です。
スクリプト:
現在接続されているデータベースにサンプルテーブルとプログラムを作成します。
テーブルのすべての権限を
PUBLICグループに付与します。
テーブルとプログラムは、現在のユーザーがテーブルとプロシージャを作成する権限を持つ検索パスの最初のスキーマに作成されます。次のコマンドを発行して、検索パスを表示できます。
SHOW SEARCH_PATH;
PSQLコマンドを使用して、検索パスを変更できます。
サンプルデータベースの説明¶
サンプルデータベースは、組織内の従業員を表します。従業員、部門、および従業員の履歴レコードの3つのタイプのレコードが含まれています。
各従業員には、識別番号、名前、雇用日、給与、および管理者がいます。一部の従業員は、給与に加えて手数料を受け取ります。すべての従業員関連情報は emp テーブルに保存されます。
サンプル会社は地域的に多様であるため、部門の場所を追跡します。各会社の従業員は部門に割り当てられます。各部門は、一意の部門番号と短縮名で識別されます。各部門は1つの場所に関連付けられています。すべての部門関連情報は dept テーブルに保存されます。
同社はまた、従業員が抱える仕事に関する情報も追跡しています。一部の従業員は、長期間にわたって会社に在籍しており、さまざまな役職に就いたり、昇給を受け取ったり、部門を切り替えたりしました。開始日、新しい役職、部門、給与、ステータス変更の理由を含む新しいジョブレコードが追加されます。すべての従業員の履歴は jobhist テーブルに保持されます。
以下は pg-sample.sql スクリプトです。
SET datestyle TO 'iso, dmy';
--
-- Script that creates the 'sample' tables, views
-- functions, triggers, etc.
--
-- Start new transaction - commit all or nothing
--
BEGIN;
--
-- Create and load tables used in the documentation examples.
--
-- Create the 'dept' table
--
CREATE TABLE dept (
deptno NUMERIC(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR(13)
);
--
-- Create the 'emp' table
--
CREATE TABLE emp (
empno NUMERIC(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMERIC(7,2),
deptno NUMERIC(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
--
-- Create the 'jobhist' table
--
CREATE TABLE jobhist (
empno NUMERIC(4) NOT NULL,
startdate TIMESTAMP(0) NOT NULL,
enddate TIMESTAMP(0),
job VARCHAR(9),
sal NUMERIC(7,2),
comm NUMERIC(7,2),
deptno NUMERIC(2),
chgdesc VARCHAR(80),
CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
REFERENCES emp(empno) ON DELETE CASCADE,
CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno) ON DELETE SET NULL,
CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
);
--
-- Create the 'salesemp' view
--
CREATE OR REPLACE VIEW salesemp AS
SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN';
--
-- Sequence to generate values for function 'new_empno'.
--
CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1;
--
-- Issue PUBLIC grants
--
--GRANT ALL ON emp TO PUBLIC;
--GRANT ALL ON dept TO PUBLIC;
--GRANT ALL ON jobhist TO PUBLIC;
--GRANT ALL ON salesemp TO PUBLIC;
--GRANT ALL ON next_empno TO PUBLIC;
--
-- Load the 'dept' table
--
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
--
-- Load the 'emp' table
--
INSERT INTO emp VALUES
(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES
(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES
(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES
(7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES
(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES
(7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES
(7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES
(7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES
(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES
(7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES
(7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES
(7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES
(7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES
(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
--
-- Load the 'jobhist' table
--
INSERT INTO jobhist VALUES
(7369,'17-DEC-80',NULL,'CLERK',800,NULL,20,'New Hire');
INSERT INTO jobhist VALUES
(7499,'20-FEB-81',NULL,'SALESMAN',1600,300,30,'New Hire');
INSERT INTO jobhist VALUES
(7521,'22-FEB-81',NULL,'SALESMAN',1250,500,30,'New Hire');
INSERT INTO jobhist VALUES
(7566,'02-APR-81',NULL,'MANAGER',2975,NULL,20,'New Hire');
INSERT INTO jobhist VALUES
(7654,'28-SEP-81',NULL,'SALESMAN',1250,1400,30,'New Hire');
INSERT INTO jobhist VALUES
(7698,'01-MAY-81',NULL,'MANAGER',2850,NULL,30,'New Hire');
INSERT INTO jobhist VALUES
(7782,'09-JUN-81',NULL,'MANAGER',2450,NULL,10,'New Hire');
INSERT INTO jobhist VALUES
(7788,'19-APR-87','12-APR-88','CLERK',1000,NULL,20,'New Hire');
INSERT INTO jobhist VALUES
(7788,'13-APR-88','04-MAY-89','CLERK',1040,NULL,20,'Raise');
INSERT INTO jobhist VALUES
(7788,'05-MAY-90',NULL,'ANALYST',3000,NULL,20,'Promoted to Analyst');
INSERT INTO jobhist VALUES
(7839,'17-NOV-81',NULL,'PRESIDENT',5000,NULL,10,'New Hire');
INSERT INTO jobhist VALUES
(7844,'08-SEP-81',NULL,'SALESMAN',1500,0,30,'New Hire');
INSERT INTO jobhist VALUES
(7876,'23-MAY-87',NULL,'CLERK',1100,NULL,20,'New Hire');
INSERT INTO jobhist VALUES
(7900,'03-DEC-81','14-JAN-83','CLERK',950,NULL,10,'New Hire');
INSERT INTO jobhist VALUES
(7900,'15-JAN-83',NULL,'CLERK',950,NULL,30,'Changed to Dept 30');
INSERT INTO jobhist VALUES
(7902,'03-DEC-81',NULL,'ANALYST',3000,NULL,20,'New Hire');
INSERT INTO jobhist VALUES
(7934,'23-JAN-82',NULL,'CLERK',1300,NULL,10,'New Hire');
--
-- Populate statistics table and view (pg_statistic/pg_stats)
--
ANALYZE dept;
ANALYZE emp;
ANALYZE jobhist;
--
-- Function that lists all employees' numbers and names
-- from the 'emp' table using a cursor.
--
CREATE OR REPLACE FUNCTION list_emp() RETURNS VOID
AS $$
DECLARE
v_empno NUMERIC(4);
v_ename VARCHAR(10);
emp_cur CURSOR FOR
SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
OPEN emp_cur;
RAISE INFO 'EMPNO ENAME';
RAISE INFO '----- -------';
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN NOT FOUND;
RAISE INFO '% %', v_empno, v_ename;
END LOOP;
CLOSE emp_cur;
RETURN;
END;
$$ LANGUAGE 'plpgsql';
--
-- Function that selects an employee row given the employee
-- number and displays certain columns.
--
CREATE OR REPLACE FUNCTION select_emp (
p_empno NUMERIC
) RETURNS VOID
AS $$
DECLARE
v_ename emp.ename%TYPE;
v_hiredate emp.hiredate%TYPE;
v_sal emp.sal%TYPE;
v_comm emp.comm%TYPE;
v_dname dept.dname%TYPE;
v_disp_date VARCHAR(10);
BEGIN
SELECT INTO
v_ename, v_hiredate, v_sal, v_comm, v_dname
ename, hiredate, sal, COALESCE(comm, 0), dname
FROM emp e, dept d
WHERE empno = p_empno
AND e.deptno = d.deptno;
IF NOT FOUND THEN
RAISE INFO 'Employee % not found', p_empno;
RETURN;
END IF;
v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY');
RAISE INFO 'Number : %', p_empno;
RAISE INFO 'Name : %', v_ename;
RAISE INFO 'Hire Date : %', v_disp_date;
RAISE INFO 'Salary : %', v_sal;
RAISE INFO 'Commission: %', v_comm;
RAISE INFO 'Department: %', v_dname;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'The following is SQLERRM : %', SQLERRM;
RAISE INFO 'The following is SQLSTATE: %', SQLSTATE;
RETURN;
END;
$$ LANGUAGE 'plpgsql';
--
-- A RECORD type used to format the return value of
-- function, 'emp_query'.
--
CREATE TYPE emp_query_type AS (
empno NUMERIC,
ename VARCHAR(10),
job VARCHAR(9),
hiredate DATE,
sal NUMERIC
);
--
-- Function that queries the 'emp' table based on
-- department number and employee number or name. Returns
-- employee number and name as INOUT parameters and job,
-- hire date, and salary as OUT parameters. These are
-- returned in the form of a record defined by
-- RECORD type, 'emp_query_type'.
--
CREATE OR REPLACE FUNCTION emp_query (
IN p_deptno NUMERIC,
INOUT p_empno NUMERIC,
INOUT p_ename VARCHAR,
OUT p_job VARCHAR,
OUT p_hiredate DATE,
OUT p_sal NUMERIC
)
AS $$
BEGIN
SELECT INTO
p_empno, p_ename, p_job, p_hiredate, p_sal
empno, ename, job, hiredate, sal
FROM emp
WHERE deptno = p_deptno
AND (empno = p_empno
OR ename = UPPER(p_ename));
END;
$$ LANGUAGE 'plpgsql';
--
-- Function to call 'emp_query_caller' with IN and INOUT
-- parameters. Displays the results received from INOUT and
-- OUT parameters.
--
CREATE OR REPLACE FUNCTION emp_query_caller() RETURNS VOID
AS $$
DECLARE
v_deptno NUMERIC;
v_empno NUMERIC;
v_ename VARCHAR;
v_rows INTEGER;
r_emp_query EMP_QUERY_TYPE;
BEGIN
v_deptno := 30;
v_empno := 0;
v_ename := 'Martin';
r_emp_query := emp_query(v_deptno, v_empno, v_ename);
RAISE INFO 'Department : %', v_deptno;
RAISE INFO 'Employee No: %', (r_emp_query).empno;
RAISE INFO 'Name : %', (r_emp_query).ename;
RAISE INFO 'Job : %', (r_emp_query).job;
RAISE INFO 'Hire Date : %', (r_emp_query).hiredate;
RAISE INFO 'Salary : %', (r_emp_query).sal;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'The following is SQLERRM : %', SQLERRM;
RAISE INFO 'The following is SQLSTATE: %', SQLSTATE;
RETURN;
END;
$$ LANGUAGE 'plpgsql';
--
-- Function to compute yearly compensation based on semimonthly
-- salary.
--
CREATE OR REPLACE FUNCTION emp_comp (
p_sal NUMERIC,
p_comm NUMERIC
) RETURNS NUMERIC
AS $$
BEGIN
RETURN (p_sal + COALESCE(p_comm, 0)) \* 24;
END;
$$ LANGUAGE 'plpgsql';
--
-- Function that gets the next number from sequence, 'next_empno',
-- and ensures it is not already in use as an employee number.
--
CREATE OR REPLACE FUNCTION new_empno() RETURNS INTEGER
AS $$
DECLARE
v_cnt INTEGER := 1;
v_new_empno INTEGER;
BEGIN
WHILE v_cnt > 0 LOOP
SELECT INTO v_new_empno nextval('next_empno');
SELECT INTO v_cnt COUNT(*) FROM emp WHERE empno = v_new_empno;
END LOOP;
RETURN v_new_empno;
END;
$$ LANGUAGE 'plpgsql';
--
-- Function that adds a new clerk to table 'emp'.
--
CREATE OR REPLACE FUNCTION hire_clerk (
p_ename VARCHAR,
p_deptno NUMERIC
) RETURNS NUMERIC
AS $$
DECLARE
v_empno NUMERIC(4);
v_ename VARCHAR(10);
v_job VARCHAR(9);
v_mgr NUMERIC(4);
v_hiredate DATE;
v_sal NUMERIC(7,2);
v_comm NUMERIC(7,2);
v_deptno NUMERIC(2);
BEGIN
v_empno := new_empno();
INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782,
CURRENT_DATE, 950.00, NULL, p_deptno);
SELECT INTO
v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE empno = v_empno;
RAISE INFO 'Department : %', v_deptno;
RAISE INFO 'Employee No: %', v_empno;
RAISE INFO 'Name : %', v_ename;
RAISE INFO 'Job : %', v_job;
RAISE INFO 'Manager : %', v_mgr;
RAISE INFO 'Hire Date : %', v_hiredate;
RAISE INFO 'Salary : %', v_sal;
RAISE INFO 'Commission : %', v_comm;
RETURN v_empno;
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'The following is SQLERRM : %', SQLERRM;
RAISE INFO 'The following is SQLSTATE: %', SQLSTATE;
RETURN -1;
END;
$$ LANGUAGE 'plpgsql';
--
-- Function that adds a new salesman to table 'emp'.
--
CREATE OR REPLACE FUNCTION hire_salesman (
p_ename VARCHAR,
p_sal NUMERIC,
p_comm NUMERIC
) RETURNS NUMERIC
AS $$
DECLARE
v_empno NUMERIC(4);
v_ename VARCHAR(10);
v_job VARCHAR(9);
v_mgr NUMERIC(4);
v_hiredate DATE;
v_sal NUMERIC(7,2);
v_comm NUMERIC(7,2);
v_deptno NUMERIC(2);
BEGIN
v_empno := new_empno();
INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698,
CURRENT_DATE, p_sal, p_comm, 30);
SELECT INTO
v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE empno = v_empno;
RAISE INFO 'Department : %', v_deptno;
RAISE INFO 'Employee No: %', v_empno;
RAISE INFO 'Name : %', v_ename;
RAISE INFO 'Job : %', v_job;
RAISE INFO 'Manager : %', v_mgr;
RAISE INFO 'Hire Date : %', v_hiredate;
RAISE INFO 'Salary : %', v_sal;
RAISE INFO 'Commission : %', v_comm;
RETURN v_empno;
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'The following is SQLERRM : %', SQLERRM;
RAISE INFO 'The following is SQLSTATE: %', SQLSTATE;
RETURN -1;
END;
$$ LANGUAGE 'plpgsql';
--
-- Rule to INSERT into view 'salesemp'
--
CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp
DO INSTEAD
INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698,
NEW.hiredate, NEW.sal, NEW.comm, 30);
--
-- Rule to UPDATE view 'salesemp'
--
CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp
DO INSTEAD
UPDATE emp SET empno = NEW.empno,
ename = NEW.ename,
hiredate = NEW.hiredate,
sal = NEW.sal,
comm = NEW.comm
WHERE empno = OLD.empno;
--
-- Rule to DELETE from view 'salesemp'
--
CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp
DO INSTEAD
DELETE FROM emp WHERE empno = OLD.empno;
--
-- After statement-level trigger that displays a message after
-- an insert, update, or deletion to the 'emp' table. One message
-- per SQL command is displayed.
--
CREATE OR REPLACE FUNCTION user_audit_trig() RETURNS TRIGGER
AS $$
DECLARE
v_action VARCHAR(24);
v_text TEXT;
BEGIN
IF TG_OP = 'INSERT' THEN
v_action := ' added employee(s) on ';
ELSIF TG_OP = 'UPDATE' THEN
v_action := ' updated employee(s) on ';
ELSIF TG_OP = 'DELETE' THEN
v_action := ' deleted employee(s) on ';
END IF;
v_text := 'User ' || USER || v_action || CURRENT_DATE;
RAISE INFO ' %', v_text;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER user_audit_trig
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH STATEMENT EXECUTE PROCEDURE user_audit_trig();
--
-- Before row-level trigger that displays employee number and
-- salary of an employee that is about to be added, updated,
-- or deleted in the 'emp' table.
--
CREATE OR REPLACE FUNCTION emp_sal_trig() RETURNS TRIGGER
AS $$
DECLARE
sal_diff NUMERIC(7,2);
BEGIN
IF TG_OP = 'INSERT' THEN
RAISE INFO 'Inserting employee %', NEW.empno;
RAISE INFO '..New salary: %', NEW.sal;
RETURN NEW;
END IF;
IF TG_OP = 'UPDATE' THEN
sal_diff := NEW.sal - OLD.sal;
RAISE INFO 'Updating employee %', OLD.empno;
RAISE INFO '..Old salary: %', OLD.sal;
RAISE INFO '..New salary: %', NEW.sal;
RAISE INFO '..Raise : %', sal_diff;
RETURN NEW;
END IF;
IF TG_OP = 'DELETE' THEN
RAISE INFO 'Deleting employee %', OLD.empno;
RAISE INFO '..Old salary: %', OLD.sal;
RETURN OLD;
END IF;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER emp_sal_trig
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_sal_trig();
COMMIT;
