9.4 beta - pg_get_viewdef() and WITH CHECK OPTION

On 17 May 2014 13:25, Thomas Kellerer <spam_eater@gmx.net> wrote:

Hello,

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.

Is that intended (if so: why?) or is this an oversight/bug?

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+

Regards,
Dean

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

On 17 May 2014 13:25, Thomas Kellerer <spam_eater@gmx.net> 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?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dean Rasheed wrote on 19.05.2014 01:10:

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.

Is that intended (if so: why?) or is this an oversight/bug?

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+

Thanks, although not the answer I hoped for :)

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)

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 19 May 2014 02:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

On 17 May 2014 13:25, Thomas Kellerer <spam_eater@gmx.net> 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@gmx.net> 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:

/messages/by-id/CAHyXU0xzs-ow4qyP+Rx8pP_dhtUeReeo3yzB7CmwKF=fv0VDBA@mail.gmail.com

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general