本节简单介绍了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) ```