Query of the Original DDL Statement for a View

Availability

This feature is available since MogDB 5.0.2.

Introduction

MogDB supports querying the original DDL statement of a view using the pg_get_ori_viewdef(view_name) function. Additionally, the DDL statement includes the comment following AS.

Benefits

You can query the original DDL statement of a view. If there are comments following AS, they are also presented in the DDL statement.

Description

MogDB adds the pg_get_ori_viewdef(view_name) function to obtain the original DDL statement of a view. The DDL can present the comments following AS. There are two kinds of comment, including "--" and "/* */".

Example

Create a test table, insert data, and query it.

MogDB=# create table t_auto(a int, b int);
CREATE TABLE
MogDB=# insert into t_auto values(1,2);
INSERT 0 1
MogDB=# select * from t_auto;
 a | b
---+---
 1 | 2
(1 row)

Create a view with "--" and "/* */" comment.

MogDB=# create /* line create*/ --line create
MogDB-# or  /* line or */   --line or
MogDB-# replace /* line replace */ --line replace
MogDB-# view  /* line view */  --line view
MogDB-# v1 /* line view_name */  --line view_name
MogDB-# as  /*line as */   --line as
MogDB$# select  /* line select */  --line select
MogDB$# *    /* line target */  --line target
MogDB$# from  /* line from */  --line from
MogDB$# t_auto    /* line rel  */  --line rel
MogDB$# ;
CREATE VIEW

Query the view using pg_get_ori_viewdef(view_name).

There are two query formats:

select pg_get_ori_viewdef ('view_name'): queries the original DDL statement of a view in the current schema.
select pg_get_ori_viewdef ('schema.view_name'): queries the original DDL statement of a view in the specified schema.

MogDB=# select pg_get_ori_viewdef ('v1');
             pg_get_ori_viewdef
---------------------------------------------
  CREATE OR REPLACE VIEW public.v1(a, b) AS +
     /*line as */   --line as               +
 select  /* line select */  --line select   +
 *    /* line target */  --line target      +
 from  /* line from */  --line from         +
 t_auto    /* line rel  */  --line rel      +
 ;
(1 row)