sql: postgreSQL9.3 sql script

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240

SELECT * from pg_class c,pg_attribute a,pg_type t where c.relname='BookKindList' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid   SELECT a.attname from pg_class c,pg_attribute a,pg_type t where c.relname='BookKindList' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid       --查询BookKindList表的字段信息 20150402 Geovin Du 涂聚文 SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where c.relname='BookKindList' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid     SELECT * FROM information_schema.columns; --查表的列表 SELECT * FROM information_schema.columns where table_catalog='geovindu' and table_schema='public' and table_name='bookkindlist'; -- select * from pg_database where datname='bookkindlist';   select datname,dattablespace from pg_database where datname='bookkindlist';   --查看数据库 select * from pg_database;   --查看表空间 select * from pg_tablespace;    --查看语言 select * from pg_language;    --查看角色用户 select * from pg_user; select * from pg_shadow; select * from pg_roles;    --查看会话进程 select * from pg_stat_activity;    --查看表 SELECT * FROM pg_tables where schemaname = 'public';    --查看表字段 select * from information_schema.columns where table_schema = 'public' and table_name = 'bookkindlist';    --查看视图 select * from pg_views where schemaname = 'public'; select * from information_schema.views where table_schema = 'public';    --查看触发器 select * from information_schema.triggers;    --查看序列 select * from information_schema.sequences where sequence_schema = 'public';     --查看约束 select * from pg_constraint where contype = 'p'    --u unique,p primary,f foreign,c check,t trigger,x exclusion select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'bookkindlist';    --查看索引 select * from pg_index ;    --查看表上存在哪些索引以及大小 select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in ( select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'bookkindlist');    SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'bookkindlist' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname;    --查看索引定义 select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'bookkindlist'; select pg_get_indexdef(b.indexrelid);    --查看过程函数定义 select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610 select * from pg_get_functiondef(24610);    --查看表大小(不含索引等信息) select pg_relation_size('bookkindlist');                         --368640 byte select pg_size_pretty(pg_relation_size('bookkindlist'))   --360 kB    --查看DB大小 select pg_size_pretty(pg_database_size('geovindu'));   --12M    --查看服务器DB运行状态 [postgres@192.168.20.165 ~]$ pg_ctl status -D $PGDATA pg_ctl: server is running (PID: 2373) /home/postgres/bin/postgres "-D" "/database/pgdata"    --查看每个DB的使用情况(读,写,缓存,更新,事务等) select * from pg_stat_database    --查看索引的使用情况 select * from pg_stat_user_indexes;    --查看表所对应的数据文件路径与大小 SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'bookkindlist';    --查看索引与相关字段及大小  SELECT n.nspname AS schema_name,         r.rolname as table_owner,        bc.relname AS table_name,        ic.relname AS index_name,        a.attname  AS column_name,        bc.relpages*8 as index_size_kb       FROM pg_namespace n,        pg_class bc,             -- base class        pg_class ic,             -- index class        pg_index i,        pg_attribute a,           -- att in base        pg_roles r   WHERE bc.relnamespace = n.oid      and i.indrelid = bc.oid      and i.indexrelid = ic.oid      and bc.relowner = r.oid      and i.indkey[0] = a.attnum      and i.indnatts = 1      and a.attrelid = bc.oid      and n.nspname = 'public'      and bc.relname = 'bookkindlist'   ORDER BY schema_name, table_name, index_name, attname;    --查看PG锁 select * from pg_locks;    备注：relpages*8 是实际所占磁盘大小    --查看表空间大小 select pg_tablespace_size('pg_default');    --查看序列与表的对应关系   WITH fq_objects AS (SELECT c.oid,c.relname AS fqname ,                            c.relkind, c.relname AS relation                     FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),         sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),      tables    AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )          SELECT        s.fqname AS sequence,        '->' as depends,        t.fqname AS table       FROM        pg_depend d JOIN sequences s ON s.oid = d.objid                  JOIN tables t ON t.oid = d.refobjid           WHERE        d.deptype = 'a' and t.fqname = 'bookkindlist';   -- select * from information_schema.columns where table_catalog= 'geovindu' AND table_name = 'bookkindlist';   select * from pg_description;         ---一个查询表结构的ＳＱＬ   SELECT     col.table_schema ,     col.table_name ,     col.ordinal_position,     col.column_name ,     col.data_type ,     col.character_maximum_length,     col.numeric_precision,     col.numeric_scale,     col.is_nullable,     col.column_default ,     des.description FROM     information_schema.columns col LEFT JOIN pg_description des         ON col.table_name::regclass = des.objoid     AND col.ordinal_position = des.objsubid WHERE     table_schema = 'public'     AND table_name = 'bookkindlist' ORDER BY     ordinal_position;     select * from pg_namespace   select * from pg_class where relname='bookkindlist' --- SELECT n.nspname , relname FROM pg_class c , pg_namespace n WHERE c.relnamespace = n.oid AND nspname='public' AND relkind = 'r' AND relhassubclass ORDER BY nspname , relname; -- select * from information_schema.columns where table_name = 'bookkindlist'; --表结构 select table_schema,table_name,column_name,udt_name,data_type,column_default,character_maximum_length,is_nullable from information_schema.columns where table_name = 'bookkindlist'; select table_schema,table_name,column_name as FieldName,udt_name,data_type as   FieldType,column_default,character_maximum_length as   FieldLength,is_nullable from information_schema.columns where table_name = 'bookkindlist'; --表主键名称 select pg_constraint.conname as pk_name from pg_constraint  inner join pg_class  on pg_constraint.conrelid = pg_class.oid where pg_class.relname = 'bookkindlist' and pg_constraint.contype='p';   --表主键字段 select pg_constraint.conname as pk_name,pg_attribute.attname as column_name,pg_type.typname as data_type,pg_class.relname as table_name, info.character_maximum_length,info.is_nullable  from pg_constraint  inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and  pg_attribute.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attribute.atttypid inner join information_schema.columns as info on info.column_name=pg_attribute.attname   where pg_class.relname = 'bookkindlist' and pg_constraint.contype='p';     --表主键名称 select conname,conrelid,connamespace from pg_constraint where contype='p';   select * from pg_constraint where contype='p'; ---表和表主键名称 select oid,relname from pg_class;   select * from pg_class where relnamespace=2200;   select * from pg_class; --表 select * from pg_type where typnamespace=2200; select typnamespace,typname,oid from pg_type where typnamespace=2200;     select * from pg_type where typname='bookkindlist'; -- 主键字段名attname   select * from pg_attribute; select * from pg_attribute where attstorage='p'; select attrelid,attname,attnum from pg_attribute where attstorage='p';

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445

---http://www.alberton.info/postgresql_meta_info.html --列出所有数据库中的表名 Detailed table field info SELECT relname    FROM pg_class   WHERE relname !~ '^(pg_|sql_)'     AND relkind = 'r';  --  SELECT c.relname AS "Name"    FROM pg_class c, pg_user u   WHERE c.relowner = u.usesysid     AND c.relkind = 'r'     AND NOT EXISTS (         SELECT 1           FROM pg_views          WHERE viewname = c.relname         )     AND c.relname !~ '^(pg_|sql_)'  UNION  SELECT c.relname AS "Name"    FROM pg_class c   WHERE c.relkind = 'r'     AND NOT EXISTS (         SELECT 1           FROM pg_views          WHERE viewname = c.relname         )     AND NOT EXISTS (         SELECT 1           FROM pg_user          WHERE usesysid = c.relowner         )     AND c.relname !~ '^pg_';  -->   -- using INFORMATION_SCHEMA:       SELECT table_name    FROM information_schema.tables   WHERE table_type = 'BASE TABLE'     AND table_schema NOT IN         ('pg_catalog', 'information_schema');          --列出所有视图 -- with postgresql 7.2:       SELECT viewname    FROM pg_views   WHERE viewname !~ '^pg_';       -- with postgresql 7.4 and later:       SELECT viewname    FROM pg_views   WHERE schemaname NOT IN         ('pg_catalog', 'information_schema')     AND viewname !~ '^pg_';       -- using INFORMATION_SCHEMA:       SELECT table_name    FROM information_schema.tables   WHERE table_type = 'VIEW'     AND table_schema NOT IN         ('pg_catalog', 'information_schema')     AND table_name !~ '^pg_';       -- or       SELECT table_name    FROM information_schema.views   WHERE table_schema NOT IN ('pg_catalog', 'information_schema')     AND table_name !~ '^pg_';    --show only the VIEWs referencing a given table             SELECT viewname          FROM pg_views  NATURAL JOIN pg_tables         WHERE tablename ='bookinfolist';   --列出所有用户 SELECT usename  FROM pg_user;   --列出某表中得所有字段 List INDICES SELECT a.attname    FROM pg_class c, pg_attribute a, pg_type t   WHERE c.relname = 'bookinfolist'     AND a.attnum > 0     AND a.attrelid = c.oid     AND a.atttypid = t.oid       -- with INFORMATION_SCHEMA:       SELECT column_name    FROM information_schema.columns   WHERE table_name = 'bookinfolist';    --列出某表字段的信息 Detailed INDEX info SELECT a.attnum AS ordinal_position,           a.attname AS column_name,           t.typname AS data_type,           a.attlen AS character_maximum_length,           a.atttypmod AS modifier,           a.attnotnull AS notnull,           a.atthasdef AS hasdefault      FROM pg_class c,           pg_attribute a,           pg_type t     WHERE c.relname = 'bookinfolist'       AND a.attnum > 0       AND a.attrelid = c.oid       AND a.atttypid = t.oid  ORDER BY a.attnum;       -- with INFORMATION_SCHEMA:         SELECT ordinal_position,           column_name,           data_type,           column_default,           is_nullable,           character_maximum_length,           numeric_precision      FROM information_schema.columns     WHERE table_name = 'bookinfolist'  ORDER BY ordinal_position;      --List INDICES SELECT relname    FROM pg_class   WHERE oid IN (      SELECT indexrelid        FROM pg_index, pg_class       WHERE pg_class.relname='bookinfolist'         AND pg_class.oid=pg_index.indrelid         AND indisunique != 't'         AND indisprimary != 't'         );     --列出表的索引信息 List CONSTRAINTs SELECT relname, indkey    FROM pg_class, pg_index   WHERE pg_class.oid = pg_index.indexrelid     AND pg_class.oid IN (      SELECT indexrelid        FROM pg_index, pg_class       WHERE pg_class.relname='bookinfolist'         AND pg_class.oid=pg_index.indrelid         AND indisunique != 't'         AND indisprimary != 't'  );      SELECT t.relname, a.attname, a.attnum       FROM pg_index c  LEFT JOIN pg_class t         ON c.indrelid  = t.oid  LEFT JOIN pg_attribute a         ON a.attrelid = t.oid        AND a.attnum = ANY(indkey)      WHERE t.relname = 'bookinfolist'        AND a.attnum = 6; -- this is the index key                    --列出表的约束 SELECT c.conname AS constraint_name,            CASE c.contype              WHEN 'c' THEN 'CHECK'              WHEN 'f' THEN 'FOREIGN KEY'              WHEN 'p' THEN 'PRIMARY KEY'              WHEN 'u' THEN 'UNIQUE'            END AS "constraint_type",            CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,            CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,            t.relname AS table_name,            array_to_string(c.conkey, ' ') AS constraint_key,            CASE confupdtype              WHEN 'a' THEN 'NO ACTION'              WHEN 'r' THEN 'RESTRICT'              WHEN 'c' THEN 'CASCADE'              WHEN 'n' THEN 'SET NULL'              WHEN 'd' THEN 'SET DEFAULT'            END AS on_update,            CASE confdeltype              WHEN 'a' THEN 'NO ACTION'              WHEN 'r' THEN 'RESTRICT'              WHEN 'c' THEN 'CASCADE'              WHEN 'n' THEN 'SET NULL'              WHEN 'd' THEN 'SET DEFAULT'            END AS on_delete,            CASE confmatchtype              WHEN 'u' THEN 'UNSPECIFIED'              WHEN 'f' THEN 'FULL'              WHEN 'p' THEN 'PARTIAL'            END AS match_type,            t2.relname AS references_table,            array_to_string(c.confkey, ' ') AS fk_constraint_key       FROM pg_constraint c  LEFT JOIN pg_class t  ON c.conrelid  = t.oid  LEFT JOIN pg_class t2 ON c.confrelid = t2.oid      WHERE t.relname = 'bookinfolist'       AND c.conname = 'bookinfolist_pkey';           -- with INFORMATION_SCHEMA:   --列出表的约束和主键列名  Detailed CONSTRAINT info    SELECT tc.constraint_name,            tc.constraint_type,            tc.table_name,            kcu.column_name,        tc.is_deferrable,            tc.initially_deferred,            rc.match_option AS match_type,            rc.update_rule AS on_update,            rc.delete_rule AS on_delete,            ccu.table_name AS references_table,            ccu.column_name AS references_field       FROM information_schema.table_constraints tc  LEFT JOIN information_schema.key_column_usage kcu         ON tc.constraint_catalog = kcu.constraint_catalog        AND tc.constraint_schema = kcu.constraint_schema        AND tc.constraint_name = kcu.constraint_name  LEFT JOIN information_schema.referential_constraints rc         ON tc.constraint_catalog = rc.constraint_catalog        AND tc.constraint_schema = rc.constraint_schema        AND tc.constraint_name = rc.constraint_name  LEFT JOIN information_schema.constraint_column_usage ccu         ON rc.unique_constraint_catalog = ccu.constraint_catalog        AND rc.unique_constraint_schema = ccu.constraint_schema        AND rc.unique_constraint_name = ccu.constraint_name      WHERE tc.table_name = 'bookinfolist'        AND tc.constraint_name = 'bookinfolist_pkey';      --列出所有主键约束序列  List sequences SELECT relname    FROM pg_class   WHERE relkind = 'S'     AND relnamespace IN (          SELECT oid            FROM pg_namespace           WHERE nspname NOT LIKE 'pg_%'             AND nspname != 'information_schema'  );        --列出所有触发器 List TRIGGERs   SELECT trg.tgname AS trigger_name    FROM pg_trigger trg, pg_class tbl   WHERE trg.tgrelid = tbl.oid     AND tbl.relname !~ '^pg_';  -- or  SELECT tgname AS trigger_name    FROM pg_trigger   WHERE tgname !~ '^pg_';       -- with INFORMATION_SCHEMA:       SELECT DISTINCT trigger_name    FROM information_schema.triggers   WHERE trigger_schema NOT IN         ('pg_catalog', 'information_schema');  ---List only the triggers for a given table: SELECT trg.tgname AS trigger_name    FROM pg_trigger trg, pg_class tbl   WHERE trg.tgrelid = tbl.oid     AND tbl.relname = 'bookinfolist';       -- with INFORMATION_SCHEMA:       SELECT DISTINCT trigger_name    FROM information_schema.triggers   WHERE event_object_table = 'bookinfolist'     AND trigger_schema NOT IN         ('pg_catalog', 'information_schema');      SELECT usename  FROM pg_user; --列出所有触发器的信息 Detailed TRIGGER info SELECT trg.tgname AS trigger_name,         tbl.relname AS table_name,         p.proname AS function_name,         CASE trg.tgtype & cast(2 as int2)           WHEN 0 THEN 'AFTER'           ELSE 'BEFORE'         END AS trigger_type,         CASE trg.tgtype & cast(28 as int2)           WHEN 16 THEN 'UPDATE'           WHEN  8 THEN 'DELETE'           WHEN  4 THEN 'INSERT'           WHEN 20 THEN 'INSERT, UPDATE'           WHEN 28 THEN 'INSERT, UPDATE, DELETE'           WHEN 24 THEN 'UPDATE, DELETE'           WHEN 12 THEN 'INSERT, DELETE'         END AS trigger_event,         CASE trg.tgtype & cast(1 as int2)           WHEN 0 THEN 'STATEMENT'           ELSE 'ROW'         END AS action_orientation    FROM pg_trigger trg,         pg_class tbl,         pg_proc p   WHERE trg.tgrelid = tbl.oid     AND trg.tgfoid = p.oid     AND tbl.relname !~ '^pg_';       -- with INFORMATION_SCHEMA:       SELECT *    FROM information_schema.triggers   WHERE trigger_schema NOT IN         ('pg_catalog', 'information_schema');    --列出所有函数List FUNCTIONs SELECT proname    FROM pg_proc pr,         pg_type tp   WHERE tp.oid = pr.prorettype     AND pr.proisagg = FALSE     AND tp.typname <> 'trigger'     AND pr.pronamespace IN (         SELECT oid           FROM pg_namespace          WHERE nspname NOT LIKE 'pg_%'            AND nspname != 'information_schema'  );       -- with INFORMATION_SCHEMA:       SELECT routine_name    FROM information_schema.routines   WHERE specific_schema NOT IN         ('pg_catalog', 'information_schema')     AND type_udt_name != 'trigger';       ---Albe Laurenz sent me the following function that is even more informative: for a function name and schema, it selects the position in the argument list, the direction, the name and the data-type of each argument. This procedure requires PostgreSQL 8.1 or later. CREATE OR REPLACE FUNCTION public.function_args(    IN funcname character varying,    IN schema character varying,    OUT pos integer,    OUT direction character,    OUT argname character varying,    OUT datatype character varying)  RETURNS SETOF RECORD AS $$DECLARE    rettype character varying;    argtypes oidvector;    allargtypes oid[];    argmodes "char"[];    argnames text[];    mini integer;    maxi integer;  BEGIN    /* get object ID of function */    SELECT INTO rettype, argtypes, allargtypes, argmodes, argnames           CASE           WHEN pg_proc.proretset           THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL)           ELSE pg_catalog.format_type(pg_proc.prorettype, NULL) END,           pg_proc.proargtypes,           pg_proc.proallargtypes,           pg_proc.proargmodes,           pg_proc.proargnames      FROM pg_catalog.pg_proc           JOIN pg_catalog.pg_namespace           ON (pg_proc.pronamespace = pg_namespace.oid)     WHERE pg_proc.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype       AND (pg_proc.proargtypes[0] IS NULL        OR pg_proc.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)       AND NOT pg_proc.proisagg       AND pg_proc.proname = funcname       AND pg_namespace.nspname = schema       AND pg_catalog.pg_function_is_visible(pg_proc.oid);         /* bail out if not found */    IF NOT FOUND THEN      RETURN;    END IF;         /* return a row for the return value */    pos = 0;    direction = 'o'::char;    argname = 'RETURN VALUE';    datatype = rettype;    RETURN NEXT;         /* unfortunately allargtypes is NULL if there are no OUT parameters */    IF allargtypes IS NULL THEN      mini = array_lower(argtypes, 1); maxi = array_upper(argtypes, 1);    ELSE      mini = array_lower(allargtypes, 1); maxi = array_upper(allargtypes, 1);    END IF;    IF maxi < mini THEN RETURN; END IF;         /* loop all the arguments */    FOR i IN mini .. maxi LOOP      pos = i - mini + 1;      IF argnames IS NULL THEN        argname = NULL;      ELSE        argname = argnames[pos];      END IF;      IF allargtypes IS NULL THEN        direction = 'i'::char;        datatype = pg_catalog.format_type(argtypes[i], NULL);      ELSE        direction = argmodes[i];        datatype = pg_catalog.format_type(allargtypes[i], NULL);      END IF;      RETURN NEXT;    END LOOP;         RETURN;  END;$$ LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER;  COMMENT ON FUNCTION public.function_args(character varying, character  varying)  IS $$For a function name and schema, this procedure selects for each  argument the following data:  - position in the argument list (0 for the return value)  - direction 'i', 'o', or 'b'  - name (NULL if not defined)  - data type$$;          --列出所有存储过程 Show PROCEDURE definition SELECT p.proname AS procedure_name,            p.pronargs AS num_args,            t1.typname AS return_type,            a.rolname AS procedure_owner,            l.lanname AS language_type,            p.proargtypes AS argument_types_oids,            prosrc AS body       FROM pg_proc p  LEFT JOIN pg_type t1 ON p.prorettype=t1.oid     LEFT JOIN pg_authid a ON p.proowner=a.oid   LEFT JOIN pg_language l ON p.prolang=l.oid      WHERE proname = :PROCEDURE_NAME;