Thread: Views->Create Script + Regular Expressions
I expect many have experienced this before. Views->Create Script does not prefix regular expressions strings with E. (e.g. E'\\d'). -- does this have something to do with "standard_conforming_strings = on"? So each time a view is tinkered with, the user must find all occurrences of the regular expression strings and re-apply the E. Is there any way to get 'Create Script' to do this automatically? -- Regards, Richard Broersma Jr.
On Tue, 2011-08-23 at 14:28 -0700, Richard Broersma wrote: > I expect many have experienced this before. > > Views->Create Script does not prefix regular expressions strings with > E. (e.g. E'\\d'). -- does this have something to do with > "standard_conforming_strings = on"? > > So each time a view is tinkered with, the user must find all > occurrences of the regular expression strings and re-apply the E. > It would be great if you could provide us a complete example. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Tue, Aug 23, 2011 at 11:18 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> It would be great if you could provide us a complete example.
Here is a sample view.
--*************
CREATE VIEW TestView AS select cmpnt_name, regexp_matches(cmpnt_name,
E'(^\\d{1,2})-([A-Z]+) *- ?(\\d{3,4})-?([A-Z]*)?') from component;
--*************
Here what Views->Create Script returns:
--*************
-- View: testview
-- DROP VIEW testview;
CREATE OR REPLACE VIEW testview ASSELECT component.cmpnt_name,
regexp_matches(component.cmpnt_name::text, '(^\\d{1,2})-([A-Z]+) *-
?(\\d{3,4})-?([A-Z]*)?'::text) AS regexp_matches FROM component;
ALTER TABLE testview OWNER TO rbroersma;
--*************
Notice what happens if I try to execute this script.
WARNING: nonstandard use of \\ in a string literal
LINE 6: ..._name, regexp_matches(component.cmpnt_name::text, '(^\\d{1,2...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
Query returned successfully with no result in 157 ms.
--
Regards,
Richard Broersma Jr.
On Wed, 2011-08-24 at 08:04 -0700, Richard Broersma wrote:
> On Tue, Aug 23, 2011 at 11:18 PM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
> > It would be great if you could provide us a complete example.
>
> Here is a sample view.
>
> --*************
> CREATE VIEW TestView AS select cmpnt_name, regexp_matches(cmpnt_name,
> E'(^\\d{1,2})-([A-Z]+) *- ?(\\d{3,4})-?([A-Z]*)?') from component;
> --*************
>
> Here what Views->Create Script returns:
>
>
>
> --*************
> -- View: testview
>
> -- DROP VIEW testview;
>
> CREATE OR REPLACE VIEW testview AS
> SELECT component.cmpnt_name,
> regexp_matches(component.cmpnt_name::text, '(^\\d{1,2})-([A-Z]+) *-
> ?(\\d{3,4})-?([A-Z]*)?'::text) AS regexp_matches
> FROM component;
>
> ALTER TABLE testview OWNER TO rbroersma;
> --*************
>
>
>
> Notice what happens if I try to execute this script.
>
> WARNING: nonstandard use of \\ in a string literal
> LINE 6: ..._name, regexp_matches(component.cmpnt_name::text, '(^\\d{1,2...
> ^
> HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
> Query returned successfully with no result in 157 ms.
>
>
There's not much we can do about this. The SELECT part is retrieved via
a call to pg_getviewdef. So, if you want to call this a bug, this is a
PostgreSQL bug (and I don't think this is).
--
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
I ran into this warning when we switched from one version of Postgres to another. <br />It complains when the view is
created,but it works fine when I select from it.<br /><br />But I don't mix E' and \\<br /><br />My views have strings
like '\\.[0-9]+\\.'<br /><br />Your view has both E' and \\ -- could it be that if you have E' you only need \, not
\\<br/><br /><br /><br /><div class="gmail_quote">On Wed, Aug 24, 2011 at 10:28 AM, Guillaume Lelarge <span
dir="ltr"><<ahref="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">On Wed, 2011-08-24 at 08:04
-0700,Richard Broersma wrote:<br /> > On Tue, Aug 23, 2011 at 11:18 PM, Guillaume Lelarge<br /> > <<a
href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>>wrote:<br /> > > It would be great if you
couldprovide us a complete example.<br /> ><br /> > Here is a sample view.<br /> ><br /> >
--*************<br/> > CREATE VIEW TestView AS select cmpnt_name, regexp_matches(cmpnt_name,<br /> >
E'(^\\d{1,2})-([A-Z]+)*- ?(\\d{3,4})-?([A-Z]*)?') from component;<br /> > --*************<br /> ><br /> >
Herewhat Views->Create Script returns:<br /> ><br /> ><br /> ><br /> > --*************<br /> > --
View:testview<br /> ><br /> > -- DROP VIEW testview;<br /> ><br /> > CREATE OR REPLACE VIEW testview AS<br
/>> SELECT component.cmpnt_name,<br /> > regexp_matches(component.cmpnt_name::text, '(^\\d{1,2})-([A-Z]+) *-<br
/>> ?(\\d{3,4})-?([A-Z]*)?'::text) AS regexp_matches<br /> > FROM component;<br /> ><br /> > ALTER TABLE
testviewOWNER TO rbroersma;<br /> > --*************<br /> ><br /> ><br /> ><br /> > Notice what happens
ifI try to execute this script.<br /> ><br /> > WARNING: nonstandard use of \\ in a string literal<br /> >
LINE6: ..._name, regexp_matches(component.cmpnt_name::text, '(^\\d{1,2...<br /> >
^<br /> > HINT: Use the escape string syntax for backslashes, e.g., E'\\'.<br /> >
Queryreturned successfully with no result in 157 ms.<br /> ><br /> ><br /><br /> There's not much we can do about
this.The SELECT part is retrieved via<br /> a call to pg_getviewdef. So, if you want to call this a bug, this is a<br
/>PostgreSQL bug (and I don't think this is).<br /><br /><br /> --<br /> Guillaume<br /> <a
href="http://blog.guillaume.lelarge.info"target="_blank">http://blog.guillaume.lelarge.info</a><br /> <a
href="http://www.dalibo.com"target="_blank">http://www.dalibo.com</a><br /><font color="#888888"><br /><br /> --<br />
Sentvia pgadmin-support mailing list (<a
href="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a>)<br/> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgadmin-support"
target="_blank">http://www.postgresql.org/mailpref/pgadmin-support</a><br/></font></blockquote></div><br />
On Wed, Aug 24, 2011 at 8:28 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > There's not much we can do about this. The SELECT part is retrieved via > a call to pg_getviewdef. So, if you want to call this a bug, this is a > PostgreSQL bug (and I don't think this is). I agree that its probably not a bug, but it is an undesirable 'feature' that adds hurdles to altering existing DDL. I expect that this would affect table and domain constraints. I'll mention this as a feature request on the general mailing list. -- Regards, Richard Broersma Jr.