我目前了解的PG插件大约有两种，一种是利用hook，另一种是建立C函数，然后在数据库中进行关联。PG本身就是最好的老师，大家有兴趣可以看一下contrib目录下的插件。

下面将对两种方式进行介绍：

1. 利用hook建立插件，hook是PG中可以对PG运行机制进行修改的一种方式，大家可以看一下我之前对PG hook的介绍：
   1. 在contrib目录下建立brother目录，建立brother.c文件和Makefile文件。具体内容见下：
      brother.c（在用户brother登录验证成功后，显示信息Welcome to the world of PostgreSQL!）:

      /*-------------------------------------------------------------------------
       *
       * brother.c
       *	  welcome to the world of PostgreSQL
       *
       *	  contrib/brother/brother.c
       *-------------------------------------------------------------------------
       */
      #include "postgres.h"
      
      #include "libpq/auth.h"
      #include "miscadmin.h"
      #include "utils/guc.h"
      
      PG_MODULE_MAGIC;
      
      void _PG_init(void);
      void _PG_fini(void);
      
      static ClientAuthentication_hook_type pre_ClientAuthentication_hook = NULL;
      
      static
      void welcome_to_pg(Port *port, int status)
      {
      	if(status == STATUS_OK && (strcmp(port->user_name, "brother") == 0))
      		printf("Welcome to the world of PostgreSQL!\n");
      }
      
      void
      _PG_init(void)
      {
              pre_ClientAuthentication_hook = ClientAuthentication_hook;
              ClientAuthentication_hook = welcome_to_pg;
      }
      
      void
      _PG_fini(void)
      {
              ClientAuthentication_hook = pre_ClientAuthentication_hook;
      }
      Makefile文件： 
      # contrib/brother/Makefile
      
      MODULE_big = brother
      OBJS = brother.o
      
      # uncomment the following two lines to enable cracklib support
      # PG_CPPFLAGS = -DUSE_CRACKLIB '-DCRACKLIB_DICTPATH="/usr/lib/cracklib_dict"'
      # SHLIB_LINK = -lcrack
      
      ifdef USE_PGXS
      PG_CONFIG = pg_config
      PGXS := $(shell $(PG_CONFIG) --pgxs)
      include $(PGXS)
      else
      subdir = contrib/brother
      top_builddir = ../..
      include $(top_builddir)/src/Makefile.global
      include $(top_srcdir)/contrib/contrib-global.mk
      endif
      

   2. 编译并安装:
      

      [postgres@localhost brother]$ make
      gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o brother.o brother.c
      gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -shared -o brother.so brother.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/hg3.0/lib',--enable-new-dtags  
      [postgres@localhost brother]$ make install
      /bin/mkdir -p '/opt/hg3.0/lib/postgresql'
      /usr/bin/install -c -m 755  brother.so '/opt/hg3.0/lib/postgresql/brother.so'
      

   3. 文件安装位置
      

      [postgres@localhost postgresql]$ pwd
      /opt/hg3.0/lib/postgresql
      [postgres@localhost postgresql]$ ll brother.so 
      -rwxr-xr-x. 1 postgres postgres 21523 Dec 23 06:27 brother.so
      

   4. 在postgresql.conf中配置
      

       shared_preload_libraries = 'brother'
      

   5. 运行，查看效果
      

      [postgres@localhost bin]$ ./pg_ctl -D ../data start
      server starting
      [postgres@localhost bin]$ LOG:  database system was shut down at 2015-12-23 06:32:38 PST
      LOG:  MultiXact member wraparound protections are now enabled
      LOG:  database system is ready to accept connections
      LOG:  autovacuum launcher started
      
      [postgres@localhost bin]$ ./psql
      psql (9.4.5)
      Type "help" for help.
      
      postgres=# create user brother;
      CREATE ROLE
      
      [postgres@localhost bin]$ ./psql postgres brother
      Welcome to the world of PostgreSQL!
      psql (9.4.5)
      Type "help" for help.
      postgres=>
      

2. 建立C函数，并建立关联
   1. 在contrib下建立目录userid，建立C文件userid.c，建立Makefile文件，建立两个SQL文件，userid--1.0.sql，userid--unpackaged--1.0.sql，以及一个control文件，userid.control。
      (1)C文件和Makefile就不介绍了，内容如下：
      

      userid.c：
      
      /*-------------------------------------------------------------------------
       *
       * userid.c
       *	  display current session user oid
       *
       *	  contrib/userid/userid.c
       *-------------------------------------------------------------------------
       */
      #include "postgres.h"
      
      #include "libpq/auth.h"
      #include "utils/guc.h"
      #include "miscadmin.h"
      
      PG_MODULE_MAGIC;
      
      PG_FUNCTION_INFO_V1(get_current_user_id);
      
      Datum
      get_current_user_id(PG_FUNCTION_ARGS)
      {
      	PG_RETURN_OID(GetSessionUserId());
      }
      Makefile： 
      # contrib/userid/Makefile
      
      MODULE_big = userid
      OBJS = userid.o
      
      EXTENSION = userid
      DATA = userid--1.0.sql userid--unpackaged--1.0.sql
      
      ifdef USE_PGXS
      PG_CONFIG = pg_config
      PGXS := $(shell $(PG_CONFIG) --pgxs)
      include $(PGXS)
      else
      subdir = contrib/userid
      top_builddir = ../..
      include $(top_builddir)/src/Makefile.global
      include $(top_srcdir)/contrib/contrib-global.mk
      endif
      

      
      (2)userid--1.0.sql是在create extension时加载的SQL文件，会执行SQL文件中的sql，内容如下：

      /* contrib/userid/userid--1.0.sql */
      
      -- complain if script is sourced in psql, rather than via CREATE EXTENSION
      \echo Use "CREATE EXTENSION userid" to load this file. \quit
      
      -- Register the function.
      CREATE SCHEMA brother;
      CREATE FUNCTION get_current_userid()
      RETURNS OID
      AS 'MODULE_PATHNAME', 'get_current_user_id'
      LANGUAGE C;
      

      
      (3) userid--unpackaged--1.0.sql是CREATE EXTENSION userid FROM unpackaged时调用的SQL文件，这个文件是在更新9.1之前版本的插件函数时使用的，后面会介绍用法。

      /* contrib/userid/userid--unpackaged--1.0.sql */
      
      -- complain if script is sourced in psql, rather than via CREATE EXTENSION
      \echo Use "CREATE EXTENSION userid FROM unpackaged" to load this file. \quit
      
      ALTER EXTENSION userid ADD schema brother;
      ALTER EXTENSION userid ADD function get_current_userid();
      

      
      (4)control文件是在create extension加载的文件，根据这个文件，PG去寻找对应的sql文件，内容如下：

      # userid extension
      comment = 'display current session user oid'
      default_version = '1.0'
      module_pathname = '$libdir/userid'
      relocatable = true
      

   2. 编译安装
      

      [postgres@localhost userid]$ make
      gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o userid.o userid.c
      gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -shared -o userid.so userid.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/hg3.0/lib',--enable-new-dtags  
      [postgres@localhost userid]$ make install
      /bin/mkdir -p '/opt/hg3.0/lib/postgresql'
      /bin/mkdir -p '/opt/hg3.0/share/postgresql/extension'
      /bin/mkdir -p '/opt/hg3.0/share/postgresql/extension'
      /usr/bin/install -c -m 755  userid.so '/opt/hg3.0/lib/postgresql/userid.so'
      /usr/bin/install -c -m 644 userid.control '/opt/hg3.0/share/postgresql/extension/'
      /usr/bin/install -c -m 644 userid--1.0.sql userid--unpackaged--1.0.sql '/opt/hg3.0/share/postgresql/extension/'
      

   3. 文件安装位置
      

      [postgres@localhost postgresql]$ pwd
      /opt/hg3.0/lib/postgresql
      [postgres@localhost postgresql]$ ll userid.so 
      -rwxr-xr-x. 1 postgres postgres 18665 Dec 23 06:48 userid.so
      
      [postgres@localhost extension]$ pwd
      /opt/hg3.0/share/postgresql/extension
      [postgres@localhost extension]$ ll userid*
      -rw-r--r--. 1 postgres postgres 329 Dec 23 06:48 userid--1.0.sql
      -rw-r--r--. 1 postgres postgres 142 Dec 23 06:48 userid.control
      -rw-r--r--. 1 postgres postgres 304 Dec 23 06:48 userid--unpackaged--1.0.sql
      

   4. 配置
      

      [postgres@localhost bin]$ ./psql 
      psql (9.4.5)
      Type "help" for help.
      
      postgres=# create extension userid;
      CREATE EXTENSION
      postgres=# select * from pg_extension ;
            extname       | extowner | extnamespace | extrelocatable | extversion | ex
      tconfig | extcondition 
      --------------------+----------+--------------+----------------+------------+---
      --------+--------------
       plpgsql            |       10 |           11 | f              | 1.0        |   
              | 
       pgfincore          |       10 |         2200 | t              | 1.1.1      |   
              | 
       pg_stat_statements |       10 |         2200 | t              | 1.2        |   
              | 
       pg_freespacemap    |       10 |         2200 | t              | 1.0        |   
              | 
       pgstattuple        |       10 |         2200 | t              | 1.2        |   
              | 
       userid             |       10 |         2200 | t              | 1.0        |   
              | 
      (6 rows)
      

   5. 运行，查看效果
      

      postgres=# select SESSION_USER;
       session_user 
      --------------
       postgres
      (1 row)
      
      postgres=# select oid from pg_authid where rolname = 'postgres';
       oid 
      -----
        10
      (1 row)
      
      postgres=# select get_current_userid();
       get_current_userid 
      --------------------
                       10
      (1 row)
      
      postgres=# \dn
        List of schemas
        Name   |  Owner   
      ---------+----------
       brother | postgres
       public  | postgres
      (2 rows)
      
      postgres=#
      

   6. 使用unpackaged.sql，这里是更新9.1之前的插件使用的，现在来模拟一下：
      

      postgres=# create schema brother;
      CREATE SCHEMA
      postgres=# CREATE FUNCTION get_current_userid()
      RETURNS OID
      AS '$libdir/userid', 'get_current_user_id'
      LANGUAGE C;
      CREATE FUNCTION
      postgres=# create extension userid with schema public from unpackaged;
      CREATE EXTENSION
      

      因为在9.1之前都是利用SQL安装的插件，没有和extension进行关联，so，在这里可以关联一下。那什么是关联呢？

   7. extension关联的意思，首先大家需要知道PG的表，pg_depend。这张表是当我们进行drop时，添加参数CASCADE会调用查询的一张表，这张表主要存储对象oid依赖的对象oid等等。下面来看一下：
      

      postgres=# select oid from pg_proc where proname = 'get_current_userid';
        oid  
      -------
       24715
      (1 row)
      
      postgres=# select oid from pg_namespace where nspname = 'brother'; 
        oid  
      -------
       24714
      (1 row)
      
      postgres=# select oid from pg_extension where extname = 'userid';
        oid  
      -------
       24716
      (1 row)
      
      postgres=# select * from pg_depend where refobjid = 24716;
       classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
      ---------+-------+----------+------------+----------+-------------+---------
          2615 | 24714 |        0 |       3079 |    24716 |           0 | e
          1255 | 24715 |        0 |       3079 |    24716 |           0 | e
      (2 rows)
      

      这个地方就是当你drop extension时，同时会删除你插件中SQL文件建立的对象的原因。那当create extension时，又是什么地方去写pg_depend信息的呢？ h.当你进行create extension时，下面这段代码就是自动建立关联的代码：

      /*
       * If we are executing a CREATE EXTENSION operation, mark the given object
       * as being a member of the extension.  Otherwise, do nothing.
       *
       * This must be called during creation of any user-definable object type
       * that could be a member of an extension.
       *
       * If isReplace is true, the object already existed (or might have already
       * existed), so we must check for a pre-existing extension membership entry.
       * Passing false is a guarantee that the object is newly created, and so
       * could not already be a member of any extension.
       */
      void
      recordDependencyOnCurrentExtension(const ObjectAddress *object,
      								   bool isReplace)
      {
      	/* Only whole objects can be extension members */
      	Assert(object->objectSubId == 0);
      
      	if (creating_extension)
      	{
      		ObjectAddress extension;
      
      		/* Only need to check for existing membership if isReplace */
      		if (isReplace)
      		{
      			Oid			oldext;
      
      			oldext = getExtensionOfObject(object->classId, object->objectId);
      			if (OidIsValid(oldext))
      			{
      				/* If already a member of this extension, nothing to do */
      				if (oldext == CurrentExtensionObject)
      					return;
      				/* Already a member of some other extension, so reject */
      				ereport(ERROR,
      						(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
      						 errmsg("%s is already a member of extension \"%s\"",
      								getObjectDescription(object),
      								get_extension_name(oldext))));
      			}
      		}
      
      		/* OK, record it as a member of CurrentExtensionObject */
      		extension.classId = ExtensionRelationId;
      		extension.objectId = CurrentExtensionObject;
      		extension.objectSubId = 0;
      
      		recordDependencyOn(object, &extension, DEPENDENCY_EXTENSION);
      	}
      }
      

   至此，就是我在学习PG源码中学习到的知识。这里介绍的还比较简单，请大家多多关注，我后续还会继续整理的。