Re: Views->Create Script + Regular Expressions

On Wed, 2011-08-24 at 08:04 -0700, Richard Broersma wrote:
> On Tue, Aug 23, 2011 at 11:18 PM, Guillaume Lelarge
> <guillaume(at)lelarge(dot)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