pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedGet original view definition without modification
2+ messages / 2 participants
[nested] [flat]
* Get original view definition without modification
@ 2020-05-29 05:55 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2020-05-29 05:55 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: shandeep2846@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/9.5/rules-views.html
Description:
I am looking a way to get exact view definition, for ex :
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);
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Get original view definition without modification
@ 2020-05-29 15:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2020-05-29 15:06 UTC (permalink / raw)
To: shandeep2846@gmail.com; +Cc: pgsql-docs@lists.postgresql.org
PG Doc comments form <noreply@postgresql.org> writes:
> I am looking a way to get exact view definition, for ex :
> ...
> 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)
No, Postgres only saves a "compiled" form of a view. Storing the original
text would have its own pitfalls, eg what if you rename a table or column
mentioned in the view? (Also, the SQL standard has some requirements that
would be difficult to meet otherwise.)
If you really want the original text, best bet is to keep your schema
creation commands in a VCS or the like, outside the database.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-05-29 15:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-29 05:55 Get original view definition without modification PG Doc comments form <noreply@postgresql.org>
2020-05-29 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox