CODEWITHSUNDEEP
viewpostgresql-9.5
Shandeep
Shandeep

Reputation: 21

Get original view definition from POSTGRESQL DB

I have created a view with the below definition :

CREATE TABLE t1(id int,name varchar);
INSERT INTO t1 values(1,'n1'),(2,'n2');
CREATE VIEW v2 AS SELECT * FROM t1 WHERE name = 'n1';

But when i checked the definition in postgresql DB(9.5) in pg_views table, it is getting modified in the below way :

postgres=# select * from pg_views where schemaname = 'sc1' and viewname = 'v2';
     schemaname | viewname | viewowner |               definition
    ------------+----------+-----------+-----------------------------------------
     sc1        | v2       | postgres  |  SELECT t1.id,                         +
                |          |           |     t1.name                            +
                |          |           |    FROM sc1.t1                         +
                |          |           |   WHERE ((t1.name)::text = 'n1'::text);
    (1 row)

I am fine with adding tablename before columnname but i don't want the extra '::text' part. Is there anyway to achieve this(like any other system table i can query from to get original definition)

Upvotes: 2

Views: 6379

Answers (1)

user330315
user330315

Reputation:

You can not get the "original" view definition from the Postgres system tables.
Postgres only stores the parsed version.

Upvotes: 2

Related Questions