ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(122) - Develop(EDB package)

PostgreSQL DBA(122) - Develop(EDB package)

原创 PostgreSQL 作者:husthxd 时间:2019-11-06 10:54:23 0 删除 编辑

本节简单介绍了edb中的package。

创建package
兼容Oracle语法

create or replace package pk_demo
as
  var_pk_demo_1 number;
  function func_demo() return number;
  procedure proc_demo();
end pk_demo;
create or replace package body pk_demo 
as
  function func_demo() return number
  AS
    BEGIN
      var_pk_demo_1 := 100;
      return var_pk_demo_1;
    END;
  procedure proc_demo() 
  AS
    ret number;
    BEGIN
      select func_demo() into ret;
      var_pk_demo_1 := 200;
      dbms_output.put_line('var_pk_demo_1 := '||var_pk_demo_1||',ret = '||ret);
    END;
END pk_demo;

元数据
1.存储组织结构
pg_namespace

postgres=# \d pg_namespace
                Table "pg_catalog.pg_namespace"
      Column      |   Type    | Collation | Nullable | Default 
------------------+-----------+-----------+----------+---------
 nspname          | name      |           | not null | 
 nspowner         | oid       |           | not null | 
 nspparent        | oid       |           | not null | 
 nspobjecttype    | oid       |           | not null | 
 nspforeignserver | oid       |           | not null | 
 nspsecdef        | boolean   |           | not null | 
 nspremoteschema  | text      |           |          | 
 nspheadsrc       | text      |           |          | 
 nspbodysrc       | text      |           |          | 
 nspacl           | aclitem[] |           |          | 
Indexes:
    "pg_namespace_nspname_index" UNIQUE, btree (nspname, nspparent)
    "pg_namespace_oid_index" UNIQUE, btree (oid)
postgres=# select * from pg_namespace where nspname='pk_demo';
 nspname | nspowner | nspparent | nspobjecttype | nspforeignserver | nspsecdef | nspremoteschema |     nspheadsrc      |     nspbodysrc      | nspacl 
---------+----------+-----------+---------------+------------------+-----------+-----------------+---------------------+---------------------+--------
 pk_demo |       10 |      2200 |             0 |                0 | t         |                 |                    +|                    +| 
         |          |           |               |                  |           |                 |   @VARIABLE 16462@;+|   @FUNCTION 16463@;+| 
         |          |           |               |                  |           |                 |   @FUNCTION 16463@;+|                    +| 
         |          |           |               |                  |           |                 |   @FUNCTION 16464@;+|   @FUNCTION 16464@;+| 
         |          |           |               |                  |           |                 |                     |                     | 
(1 row)
postgres=# select * from pg_user where usesysid=10;
   usename    | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useaccountstatus | uselockdate | usepasswordexpire | useconfig 
--------------+----------+-------------+----------+---------+--------------+----------+----------+------------------+-------------+-------------------+-----------
 enterprisedb |       10 | t           | t        | t       | t            | ******** |          |                0 |             |                   | 
(1 row)
postgres=# select * from pg_namespace where oid=2200;
 nspname | nspowner | nspparent | nspobjecttype | nspforeignserver | nspsecdef | nspremoteschema | nspheadsrc | nspbodysrc |                     nspacl                      
---------+----------+-----------+---------------+------------------+-----------+-----------------+------------+------------+-------------------------------------------------
 public  |       10 |         0 |             0 |                0 | f         |                 |            |            | {enterprisedb=UC/enterprisedb,=UC/enterprisedb}
(1 row)

2.变量
edb_variable

postgres=# \d edb_var*
               Table "pg_catalog.edb_variable"
      Column      |  Type   | Collation | Nullable | Default 
------------------+---------+-----------+----------+---------
 varname          | name    |           | not null | 
 varpackage       | oid     |           | not null | 
 vartype          | oid     |           | not null | 
 vartypmod        | integer |           | not null | 
 varaccess        | "char"  |           | not null | 
 varisconst       | boolean |           | not null | 
 varseq           | integer |           | not null | 
 varerrcode       | integer |           | not null | 
 varsrc           | text    |           |          | 
 varexceptionname | text    |           |          | 
Indexes:
    "pg_variable_oid_index" UNIQUE, btree (oid)
    "pg_variable_varname_pkg_index" UNIQUE, btree (varpackage, varname)
postgres=# select * from edb_variable where oid=16462;
    varname    | varpackage | vartype | vartypmod | varaccess | varisconst | varseq | varerrcode | varsrc | varexceptionname 
---------------+------------+---------+-----------+-----------+------------+--------+------------+--------+------------------
 var_pk_demo_1 |      16454 |    1700 |        -1 | +         | f          |      1 |          0 |        | 
(1 row)

3.函数/过程
pg_proc

postgres=# \d pg_proc
                     Table "pg_catalog.pg_proc"
       Column        |     Type     | Collation | Nullable | Default 
---------------------+--------------+-----------+----------+---------
 proname             | name         |           | not null | 
 pronamespace        | oid          |           | not null | 
 proowner            | oid          |           | not null | 
 prolang             | oid          |           | not null | 
 procost             | real         |           | not null | 
 prorows             | real         |           | not null | 
 provariadic         | oid          |           | not null | 
 protransform        | regproc      |           | not null | 
 proisagg            | boolean      |           | not null | 
 proiswindow         | boolean      |           | not null | 
 prosecdef           | boolean      |           | not null | 
 proleakproof        | boolean      |           | not null | 
 proisstrict         | boolean      |           | not null | 
 proretset           | boolean      |           | not null | 
 proisweak           | boolean      |           | not null | 
 provolatile         | "char"       |           | not null | 
 proparallel         | "char"       |           | not null | 
 protype             | "char"       |           | not null | 
 proaccess           | "char"       |           | not null | 
 pronargs            | smallint     |           | not null | 
 pronargdefaults     | smallint     |           | not null | 
 prolineno           | integer      |           | not null | 
 prorettype          | oid          |           | not null | 
 proargtypes         | oidvector    |           | not null | 
 promemberattrs      | text         |           |          | 
 proallargtypes      | oid[]        |           |          | 
 proargmodes         | "char"[]     |           |          | 
 proargdeclaredmodes | "char"[]     |           |          | 
 proargnames         | text[]       |           |          | 
 proargdefaults      | pg_node_tree |           |          | 
 protrftypes         | oid[]        |           |          | 
 prosrc              | text         |           | not null | 
 probin              | text         |           |          | 
 proconfig           | text[]       |           |          | 
 proacl              | aclitem[]    |           |          | 
Indexes:
    "pg_proc_oid_index" UNIQUE, btree (oid)
    "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, protype, proargtypes, pronamespace)
postgres=# select proname,pronamespace from pg_proc where oid=16463;
  proname  | pronamespace 
-----------+--------------
 func_demo |        16454
(1 row)
postgres=# select proname,pronamespace from pg_proc where oid=16464;
  proname  | pronamespace 
-----------+--------------
 proc_demo |        16454
(1 row)
postgres=#

4.相关视图:edb_pkgelements、edb_package


postgres=# \d edb_pkg*
                View "pg_catalog.edb_pkgelements"
   Column    |       Type        | Collation | Nullable | Default 
-------------+-------------------+-----------+----------+---------
 packageoid  | oid               |           |          | 
 eltname     | name              |           |          | 
 visibilty   | "char"            |           |          | 
 eltclass    | character varying |           |          | 
 eltdatatype | oid               |           |          | 
 nargs       | smallint          |           |          | 
 argtypes    | oidvector         |           |          | 
 argmodes    | "char"[]          |           |          | 
 argnames    | text[]            |           |          | 
 argdefvals  | pg_node_tree      |           |          | 
postgres=# select * from edb_pkgelements where packageoid = 16454;
 packageoid |    eltname    | visibilty | eltclass | eltdatatype | nargs | argtypes | argmodes | argnames | argdefvals 
------------+---------------+-----------+----------+-------------+-------+----------+----------+----------+------------
      16454 | proc_demo     | +         | P        |        2278 |     0 |          |          |          | 
      16454 | var_pk_demo_1 | +         | V        |        1700 |       |          |          |          | 
      16454 | func_demo     | +         | F        |        1700 |     0 |          |          |          | 
(3 rows)
postgres=# 
postgres=# select * from pg_views where viewname='edb_pkgelements';
 schemaname |    viewname     |  viewowner   |                         definition                         
------------+-----------------+--------------+------------------------------------------------------------
 pg_catalog | edb_pkgelements | enterprisedb |  SELECT edb_variable.varpackage AS packageoid,            +
            |                 |              |     edb_variable.varname AS eltname,                      +
            |                 |              |     edb_variable.varaccess AS visibilty,                  +
            |                 |              |     'V'::character varying AS eltclass,                   +
            |                 |              |     edb_variable.vartype AS eltdatatype,                  +
            |                 |              |     NULL::smallint AS nargs,                              +
            |                 |              |     NULL::oidvector AS argtypes,                          +
            |                 |              |     NULL::"char"[] AS argmodes,                           +
            |                 |              |     NULL::text[] AS argnames,                             +
            |                 |              |     NULL::pg_node_tree AS argdefvals                      +
            |                 |              |    FROM edb_variable                                      +
            |                 |              | UNION                                                     +
            |                 |              |  SELECT pg_proc.pronamespace AS packageoid,               +
            |                 |              |     pg_proc.proname AS eltname,                           +
            |                 |              |     pg_proc.proaccess AS visibilty,                       +
            |                 |              |         DECODE(  (pg_proc.protype)::character varying     +
            |                 |              |             , ('0'::text)::character varying              +
            |                 |              |             , ('F'::text)::character varying              +
            |                 |              |             , ('1'::text)::character varying              +
            |                 |              |             , ('P'::text)::character varying              +
            |                 |              |             , NULL::character varying                     +
            |                 |              |         ) AS eltclass,                                    +
            |                 |              |     pg_proc.prorettype AS eltdatatype,                    +
            |                 |              |     pg_proc.pronargs AS nargs,                            +
            |                 |              |     pg_proc.proargtypes AS argtypes,                      +
            |                 |              |     pg_proc.proargmodes AS argmodes,                      +
            |                 |              |     pg_proc.proargnames AS argnames,                      +
            |                 |              |     pg_proc.proargdefaults AS argdefvals                  +
            |                 |              |    FROM pg_proc                                           +
            |                 |              |   WHERE (pg_proc.pronamespace IN ( SELECT pg_namespace.oid+
            |                 |              |            FROM pg_namespace                              +
            |                 |              |           WHERE (pg_namespace.nspparent <> (0)::oid)));
(1 row)
postgres=# select * from pg_views where viewname='edb_package';
 schemaname |  viewname   |  viewowner   |                                         definition                                          
------------+-------------+--------------+---------------------------------------------------------------------------------------------
 pg_catalog | edb_package | enterprisedb |  SELECT pg_namespace.oid,                                                                  +
            |             |              |     pg_namespace.nspname AS pkgname,                                                       +
            |             |              |     pg_namespace.nspparent AS pkgnamespace,                                                +
            |             |              |     pg_namespace.nspowner AS pkgowner,                                                     +
            |             |              |     edb_get_packageheaddef(pg_namespace.oid) AS pkgheadsrc,                                +
            |             |              |     edb_get_packagebodydef(pg_namespace.oid) AS pkgbodysrc,                                +
            |             |              |     'P'::character(1) AS pkgproperties,                                                    +
            |             |              |     pg_namespace.nspacl AS pkgacl,                                                         +
            |             |              |     pg_namespace.cmin,                                                                     +
            |             |              |     pg_namespace.xmin,                                                                     +
            |             |              |     pg_namespace.cmax,                                                                     +
            |             |              |     pg_namespace.xmax,                                                                     +
            |             |              |     pg_namespace.ctid                                                                      +
            |             |              |    FROM pg_namespace                                                                       +
            |             |              |   WHERE ((pg_namespace.nspparent <> (0)::oid) AND (pg_namespace.nspobjecttype = (0)::oid));
(1 row)
postgres=# select * from edb_package where pkgname='pk_demo';
  oid  | pkgname | pkgnamespace | pkgowner |                 pkgheadsrc                  |                                   pkgbodysrc                                    | pkgproperties | pkgacl | cmin | xmin | cmax | xmax |  ctid  
-------+---------+--------------+----------+---------------------------------------------+---------------------------------------------------------------------------------+---------------+--------+------+------+------+------+--------
 16454 | pk_demo |         2200 |       10 | CREATE OR REPLACE PACKAGE public.pk_demo IS+| CREATE OR REPLACE PACKAGE BODY public.pk_demo IS                               +| P             |        |    2 | 1231 |    2 |    0 | (0,29)
       |         |              |          |   var_pk_demo_1 numeric;                   +|   FUNCTION func_demo() RETURN numeric IS                                       +|               |        |      |      |      |      | 
       |         |              |          |   FUNCTION func_demo() RETURN numeric;     +|     BEGIN                                                                      +|               |        |      |      |      |      | 
       |         |              |          |   PROCEDURE proc_demo();                   +|       var_pk_demo_1 := 100;                                                    +|               |        |      |      |      |      | 
       |         |              |          | END                                         |       return var_pk_demo_1;                                                    +|               |        |      |      |      |      | 
       |         |              |          |                                             |     END;                                                                       +|               |        |      |      |      |      | 
       |         |              |          |                                             |                                                                                +|               |        |      |      |      |      | 
       |         |              |          |                                             |   PROCEDURE proc_demo() IS                                                     +|               |        |      |      |      |      | 
       |         |              |          |                                             |     ret number;                                                                +|               |        |      |      |      |      | 
       |         |              |          |                                             |     BEGIN                                                                      +|               |        |      |      |      |      | 
       |         |              |          |                                             |       select func_demo() into ret;                                             +|               |        |      |      |      |      | 
       |         |              |          |                                             |       var_pk_demo_1 := 200;                                                    +|               |        |      |      |      |      | 
       |         |              |          |                                             |       dbms_output.put_line('var_pk_demo_1 := '||var_pk_demo_1||',ret = '||ret);+|               |        |      |      |      |      | 
       |         |              |          |                                             |     END;                                                                       +|               |        |      |      |      |      | 
       |         |              |          |                                             | END                                                                             |               |        |      |      |      |      | 
(1 row)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2662858/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1426
  • 访问量
    3861749