PostgreSQL学习笔记：数据库内查询PG的表结构定义

需要利用到plperlu和自己写一个system函数。

-bash-4.2$ psql
psql (9.6.2)
Type "help" for help.
 
postgres=# create extension plperlu;
CREATE EXTENSION
postgres=# \dx
                      List of installed extensions
  Name   | Version |   Schema   |              Description               
---------+---------+------------+----------------------------------------
 plperlu | 1.0     | pg_catalog | PL/PerlU untrusted procedural language
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# CREATE OR REPLACE FUNCTION system(text) RETURNS text
postgres-# AS 'my $cmd=shift; return `cd /tmp;$cmd`;' LANGUAGE plperlu;
CREATE FUNCTION
postgres=# 
postgres=# 

postgres=# select system('pg_dump -s -t orasup_test1 dbinfo2 |egrep -v "^--|^$"');
                       system                        
-----------------------------------------------------
 SET statement_timeout = 0;                         +
 SET lock_timeout = 0;                              +
 SET idle_in_transaction_session_timeout = 0;       +
 SET client_encoding = 'UTF8';                      +
 SET standard_conforming_strings = on;              +
 SET check_function_bodies = false;                 +
 SET client_min_messages = warning;                 +
 SET row_security = off;                            +
 SET search_path = public, pg_catalog;              +
 SET default_tablespace = '';                       +
 SET default_with_oids = false;                     +
 CREATE TABLE orasup_test1 (                        +
     a integer,                                     +
     b character varying(200)                       +
 );                                                 +
 ALTER TABLE orasup_test1 OWNER TO djidba_rw;       +
 CREATE INDEX idx_b ON orasup_test1 USING btree (b);+
 
(1 row)
 
postgres=# 
postgres=# 

文章来源：https://oracleblog.org/study-note/how-to-get-table-ddl-inside-postgresql/