From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Thomas Kellerer <spam_eater(at)gmx(dot)net>, pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION |
Date: | 2014-05-19 08:02:54 |
Message-ID: | CAEZATCU83mYBgrRZxp6mf1G806+CdAsUvgZnENx+q=ph7iCW0w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 19 May 2014 02:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
>> On 17 May 2014 13:25, Thomas Kellerer <spam_eater(at)gmx(dot)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(at)gmx(dot)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:
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2014-05-19 08:12:32 | Re: Re: [GENERAL] Is it typo in pg_stat_replication column name in PG 9.4 ? |
Previous Message | Thomas Kellerer | 2014-05-19 06:04:17 | Re: 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION |