Get original view definition from POSTGRESQL DB

Question

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)

Just take the source code out of your version control system. — user330315
– user330315, Commented May 29, 2020 at 7:22
@a_horse_with_no_name, do u mean getting source code of postgresql and modifying? can u please elaborate on this — Shandeep
– Shandeep, Commented May 29, 2020 at 8:34
No, I mean your source code. You should have the original CREATE VIEW statement in your version control system. — user330315
– user330315, Commented May 29, 2020 at 8:45
yes, in our source code we have some parsing rules for select query of CREATE VIEW statement where ':: text', these r not allowed and i don't want to change these existing ones as it might disrupt other queries. so i had 2 options in my find : 1.Get original definition from postgres 2.Modify the definition we get from postgresql. FYI : we won't store definition in ours, we just get these info from postgresql itself using jdbc connection. — Shandeep
– Shandeep, Commented May 29, 2020 at 8:55
"Get original definition from postgres" is simply not possible, when you create a view, it is parsed by Postgres and only the parsed version is stored. You have to get it from your version control system (git, subversion, ...). Not managing your code in a version control system is a really, really bad idea to begin with (and this is only one example on why it's a bad idea) — user330315
– user330315, Commented May 29, 2020 at 9:02

user330315 · Accepted Answer · 2020-05-29 09:04:05Z

2

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

answered May 29, 2020 at 9:04

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get original view definition from POSTGRESQL DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related