System Architecture

• Most internal functionality stored in system catalogues/data dictionary
• Functions, operators, aggregates, triggers, views, rules, access methods, indices, database definitions, table definitions, inheritance definitions, referential integrity, datatypes, users & statistics
• stored in pg_ * tables
• can be manipulated by users
• backend can load shared libraries dynamically

• User defined functions: CREATE FUNCTION
• Task: produce a function to 1) set up an employee profile 2) increase number of employees in the new employee's department 3) determine and record whether he/she is an executive
• SQL:

CREATE FUNCTION new_emp(int,text,text,int) RETURNS int AS '
                INSERT INTO employees VALUES($1,$2,$3);
                UPDATE employeecount SET n=n+1 WHERE department=$4;
                SELECT 1;
                ' LANGUAGE 'sql';

• Bugs/short comings
• If department is not in employeecount, count not registered
• Cannot perform (3): no conditional outside of SELECT
• PL/PgSQL:

01   CREATE FUNCTION new_emp(int,text,text,int,bool) RETURNS int AS '
02   DECLARE
03       empid ALIAS FOR $1; -- employee ID --
04       firstname ALIAS FOR $2;
05       lastname ALIAS FOR $3;
06       dep ALIAS FOR $4; -- department the employee is in --
07       exec ALIAS FOR $5;
08       depchk employeecount%ROWTYPE;
09
10   BEGIN
11       INSERT INTO employees VALUES(empid,firstname,lastname);
12       SELECT INTO depchk * FROM employeecount WHERE department=dep;
13       IF NOT FOUND THEN
14           -- department not in the relation yet, first employee
15           INSERT INTO employeecount VALUES(dep,1);
16       ELSE
17           UPDATE employeecount SET n=n+1 WHERE department=dep;
18       END IF;
19       IF exec = ''t'' THEN
20           INSERT INTO execs VALUES(empid);
21       END IF;
22       RETURN 1;
23   END;
24   ' LANGUAGE 'plpgsql';

• C - Server Programming Interface (SPI)

01   #include "executor/spi.h"
02   #include "postgres.h"
03
04   #include <string.h>
05
06   PG_FUNCTION_INFO_V1(add_emp)
07
08   Datum
09   add_emp(PG_FUNCTION_ARGS)
10   {
11      /* get the function arguments */
12
13      long int empid = PG_GETARG_INT32(0);
14      text *fn = PG_GETARG_TEXT_P(1);
15      text *ln = PG_GETARG_TEXT_P(2);
16      long int dep = PG_GETARG_INT32(3);
17      char exec = PG_GETARG_BOOL(4);
18      char query[8192];
19      int ret = 0;
20
21
22      if(SPI_connect() & 0) {
23              elog(ERROR,"SPI_connect() failed: could not connect\n");
24      }
25
26      snprintf(query,8192,"INSERT INTO employees VALUES(%li,'%s','%s');",
27              empid,VARDATA(fn),VARDATA(ln));
28      elog(NOTICE,"fn = %s and ln = %s\n",VARDATA(fn),VARDATA(ln));
29
30      if(SPI_exec(query,0) != SPI_OK_INSERT) {
31              elog(ERROR,"Error inserting into employees\n");
32      }
33
34      snprintf(query,8192,"SELECT 1 FROM employeecount WHERE department=%li",
35              dep);
36
37      if(SPI_exec(query,1) != SPI_OK_SELECT) {
38              elog(ERROR,"Error selecting from employeecount\n");
39      }
40      if(SPI_processed == 1) {
41              /* a tuple was returned, lets update */
42              snprintf(query,8192,"UPDATE employeecount SET n=n+1
43                       WHERE department=%li",dep);
44              if(SPI_exec(query,0) != SPI_OK_UPDATE) {
45                      elog(ERROR,"Error updating employeecount\n");
46              }
47      } else {
48              /* first employee for this department */
49              snprintf(query,8192,"INSERT INTO employeecount VALUES(%li,1);",dep);
50           if(SPI_exec(query,0) != SPI_OK_INSERT) {
51               elog(ERROR,"Error inserting into employeecounnt\n");
52              }
53      }
54
55      if(exec == 1) {
56           snprintf(query,8192,"INSERT INTO exec VALUES(%li);",empid);
57           if(SPI_exec(query,0) != SPI_OK_INSERT) {
58               elog(ERROR,"Error inserting into exec\n");
59           }
60      }
61      PG_RETURN_INT32(1);
62   }

• User defined types: CREATE TYPE
• User defined operators: CREATE OPERATOR
• User defined aggregates: CREATE AGGREGATE
• User defined triggers: CREATE TRIGGER