Thread: Views->Create Script + Regular Expressions

Views->Create Script + Regular Expressions

From
Richard Broersma
Date:
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.


Re: Views->Create Script + Regular Expressions

From
Guillaume Lelarge
Date:
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



Re: Views->Create Script + Regular Expressions

From
Richard Broersma
Date:
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.


Re: Views->Create Script + Regular Expressions

From
Guillaume Lelarge
Date:
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



Re: Views->Create Script + Regular Expressions

From
Michael Shapiro
Date:
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 /> 

Re: Views->Create Script + Regular Expressions

From
Richard Broersma
Date:
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.