Обсуждение: 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.
It complains when the view is created, but it works fine when I select from it.

But I don't mix E' and \\

My views have strings like  '\\.[0-9]+\\.'

Your view has both E' and \\ -- could it be that if you have E' you only need \, not \\

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.