On 19 May 2014 02:35, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Dean Rasheed <dean.a.rasheed@xxxxxxxxx> writes: >> On 17 May 2014 13:25, Thomas Kellerer <spam_eater@xxxxxxx> wrote: >>> when playing with 9.4 beta I noticed that the result of pg_get_viewdef() >>> will not include the new WITH CHECK OPTION clause when the view was created >>> using it. > >> Yes, that's correct. pg_get_viewdef() only returns the underlying >> SELECT command for a view. This does not include any of the view's >> WITH parameters (check option and/or security barrier flag), because >> they aren't allowed in a SELECT statement. > >> The additional parameters are held in pg_class.reloptions, and can be >> displayed from psql using \d+ > > I have to concur with the OP that this seems like a pretty darn weird > design choice. reloptions are for nonstandard PG-specific options, not > for SQL-spec-mandated syntax. What was the rationale for doing it like > that? > Well I think the question of where to store this option is kind of independent from the OP's question, which was about what pg_get_viewdef() should return. pg_get_viewdef() is currently documented as returning the underlying SELECT command for the view; it's used in pg_views.definition to show the "reconstructed SELECT query" and in the view_definition column of information_schema.views for the same purpose. In that latter case, there is a separate check_option column to show the value of WITH CHECK OPTION. So the SQL-spec would appear to mandate that the check option be kept separate from the view definition, which I think makes sense, because then the view definition remains a legal SQL SELECT command. Thomas Kellerer <spam_eater@xxxxxxx> wrote: > I do think it would be a good thing to then have something like pg_get_full_viewdef (and a pg_get_full_tabledef() as well) There was a discussion about adding something like that recently on -hackers in the context of pg_dump: http://www.postgresql.org/message-id/flat/CAHyXU0xzs-ow4qyP+Rx8pP_dhtUeReeo3yzB7CmwKF=fv0VDBA@xxxxxxxxxxxxxx#CAHyXU0xzs-ow4qyP+Rx8pP_dhtUeReeo3yzB7CmwKF=fv0VDBA@xxxxxxxxxxxxxx and I agree that there is a strong case for that kind of an API, and not just for tables and views or for pg_dump, as Merlin points out. There's still a lot of work to do to get the design right though. Regards, Dean
