Bristol and Bath Linux: Bristol Digest, Vol 655, Issue 4

Send Bristol mailing list submissions to
bristol@mailman.lug.org.uk

To subscribe or unsubscribe via the World Wide Web, visit
https://mailman.lug.org.uk/mailman/listinfo/bristol
or, via email, send a message with subject or body 'help' to
bristol-request@mailman.lug.org.uk

You can reach the person managing the list at
bristol-owner@mailman.lug.org.uk

When replying, please edit your Subject line so it is more specific
than "Re: Contents of Bristol digest..."

Today's Topics:

1. Re: Regex and Postgres from Perl (Chris)
2. Re: Regex and Postgres from Perl (Martin Moore)
3. Re: Regex and Postgres from Perl (Martin Moore)

----------------------------------------------------------------------

Message: 1
Date: Wed, 03 Aug 2016 08:34:11 +0100
From: Chris <cshorler@googlemail.com>
To: Martin Moore <martinm@it-helps.co.uk>, Bristol and Bath Linux User
Group <bristol@mailman.lug.org.uk>, Martin Moore via Bristol
<bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Regex and Postgres from Perl
Message-ID: <2804A315-E6E4-47A8-9184-5C8C9DE756F5@googlemail.com>
Content-Type: text/plain; charset=UTF-8

On 2 August 2016 18:04:04 BST, Martin Moore via Bristol <bristol@mailman.lug.org.uk> wrote:
>
> I'm converting an archaic system from a bit of SQL and a lot of
> Perl/Javascript to mostly SQL (Postgres) and a bit of js.
>
> Nearly done, but I've got to the bit where Regexes are involved.
>
>Basically I need to do the following from Perl into PostgreSQL. I can't
>see
>a way to do it. I guess I could possibly use the Perl module in
>Postgres but
>it's not something I've done before. Could also get away with putting
>the
> Regex stuff into js if all else fails.
>
>I've looked at regexp_split_to_array but can't see it'll do what I
>need.
>
> Any ideas?
>
> Ta,
>
> Martin.
>
>
>
>
>my $tafre = '(?:TAF(?:\s*AMD|COR|CC\w?)?\b)'; # TAF start,
>Canadian
>my $timestampre = '\d{4,6}Z?\b'; # Opt, with opt
>date
> my $icaore = '[A-Z]\w{3}\b'; # Mand
> my $latere = 'AMD|RTD|COR|TAF|AMD\sCOR';
> my $issuere = '\d{4,6}Z\b';
> my $periodre = '(?:\d{4}\/\d{4}\b|\d{4,6}\b)';
> my $atre = '\d{2,4}'; #
> my $winddirre = '[\d\/]{3}|VRB';
> my $windspdre = '[\d\/]{2}';
> my $gustre = 'G(\d{2})';
> my $windunitre = 'KT|MPS|KMH';
> my $windre = "($winddirre)($windspdre)(?:$gustre)?($windunitre)";
> my $varre = '\d{3}V\d{3}\s*';
> my $tempre = 'M?\d\d|\/\/';
> my $dewpre = 'M?\d\d|\/\/';
> my $altimeterre = '[QA]\s?\d{4}|QFE\d{3,4}.\d';
> my $visandcloud = 'CAVOK';
> my $onevisre = '(?:P?\d{4}[NSEW]{0,2}|[P0-9\s\/]{0,5}\s*SM|CAVOK)';
> my $onervrre = '(?:R\d{2}\w?\/\w+)';
>my $onecloudre =
>'(?:[A-Z]{3}[0-9\/]{3}[A-Z]{0,3}\s*|VV[0-9\/]{3}\s*)';
> my $cloudre = $onecloudre.'+|CLR|SKC|NSC';
> my $onewxre = '(?:NSW|
> .
> .
> .
> .
> .
>
> $self->{timestamp},
> $self->{icao},
> $self->{late},
> $self->{issued},
> $self->{period},
> $self->{winddir},
> $self->{meanwindsp},
> $self->{gust},
> $self->{windunit},
> $self->{vis},
> $self->{wx},
> $self->{cloud},
> $self->{supp},
> $self->{change},
> $self->{temps}
> )
>
> = ($metline =~ /
> (?:$tafre)?\s*
> ($timestampre)?\s* # optional
> timestamp
> ($icaore)\s* # mand icao
> identifier
> ($latere)?\s*
> ($issuere)?\s* # optional issue
> time
> ($periodre)\s* # Mandatory period
> (?:$windre)?\s* # wind, gust
> # ($onevisre\s*)?\s* # a vis gps
> ((?:$onevisre\s*)*)\s* # zero or more vis
> gps
> ((?:$onewxre\s*)*)\s* # zero or more wx
> groups
> ($cloudre)?\s* # optional cloud
> group
> ((?:$onesuppre\s*)*\s*)
> (.*?)\s*
> ((?:$onetaftempre\s*)*\s*)=/sx))
> # supplementary info
>
>
>
>
>
>
>
>_______________________________________________
>Bristol mailing list
>Bristol@mailman.lug.org.uk
>https://mailman.lug.org.uk/mailman/listinfo/bristol

Perhaps the function regexp_matches(...) is what you want?

https://www.postgresql.org/docs/9.5/static/functions-string.html

------------------------------

Message: 2
Date: Wed, 03 Aug 2016 11:08:46 +0100
From: Martin Moore <martinm@it-helps.co.uk>
To: Bristol and Bath Linux User Group <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Regex and Postgres from Perl
Message-ID: <69B3FB93-D906-45E4-93D5-6C48C0FE4C23@it-helps.co.uk>
Content-Type: text/plain; charset="UTF-8"

> Perhaps the function regexp_matches(...) is what you want?

Worth a look, cheers!

On 03/08/2016, 08:34, "Chris" <cshorler@googlemail.com> wrote:

------------------------------

Message: 3
Date: Wed, 03 Aug 2016 11:09:15 +0100
From: Martin Moore <martinm@it-helps.co.uk>
To: Bristol and Bath Group <bristol@mailman.lug.org.uk>
Subject: Re: [bristol] Regex and Postgres from Perl
Message-ID: <B9BD16DB-5AB3-4AD6-9A28-4EED0826B540@it-helps.co.uk>
Content-Type: text/plain; charset="utf-8"

I'll look at that too!

Cheers.

From: <parsnip@amias.net> on behalf of Amias Channer <me@amias.net>
Date: Tuesday, 2 August 2016 at 20:50
To: Bristol and Bath Linux User Group <bristol@mailman.lug.org.uk>, Martin Moore <martinm@it-helps.co.uk>
Subject: Re: [bristol] Regex and Postgres from Perl

Hello martin,

This will help
https://github.com/TooTallNate/pcre-to-regexp

Cheers
Amias

On 2 Aug 2016 18:04, "Martin Moore via Bristol" <bristol@mailman.lug.org.uk> wrote:

I'm converting an archaic system from a bit of SQL and a lot of
Perl/Javascript to mostly SQL (Postgres) and a bit of js.

Nearly done, but I've got to the bit where Regexes are involved.

Basically I need to do the following from Perl into PostgreSQL. I can't see
a way to do it. I guess I could possibly use the Perl module in Postgres but
it's not something I've done before. Could also get away with putting the
Regex stuff into js if all else fails.

I've looked at regexp_split_to_array but can't see it'll do what I need.

Any ideas?

Ta,

Martin.

my $tafre = '(?:TAF(?:\s*AMD|COR|CC\w?)?\b)'; # TAF start, Canadian
my $timestampre = '\d{4,6}Z?\b'; # Opt, with opt date
my $icaore = '[A-Z]\w{3}\b'; # Mand
my $latere = 'AMD|RTD|COR|TAF|AMD\sCOR';
my $issuere = '\d{4,6}Z\b';
my $periodre = '(?:\d{4}\/\d{4}\b|\d{4,6}\b)';
my $atre = '\d{2,4}'; #
my $winddirre = '[\d\/]{3}|VRB';
my $windspdre = '[\d\/]{2}';
my $gustre = 'G(\d{2})';
my $windunitre = 'KT|MPS|KMH';
my $windre = "($winddirre)($windspdre)(?:$gustre)?($windunitre)";
my $varre = '\d{3}V\d{3}\s*';
my $tempre = 'M?\d\d|\/\/';
my $dewpre = 'M?\d\d|\/\/';
my $altimeterre = '[QA]\s?\d{4}|QFE\d{3,4}.\d';
my $visandcloud = 'CAVOK';
my $onevisre = '(?:P?\d{4}[NSEW]{0,2}|[P0-9\s\/]{0,5}\s*SM|CAVOK)';
my $onervrre = '(?:R\d{2}\w?\/\w+)';
my $onecloudre = '(?:[A-Z]{3}[0-9\/]{3}[A-Z]{0,3}\s*|VV[0-9\/]{3}\s*)';
my $cloudre = $onecloudre.'+|CLR|SKC|NSC';
my $onewxre = '(?:NSW|
.
.
.
.
.

$self->{timestamp},
$self->{icao},
$self->{late},
$self->{issued},
$self->{period},
$self->{winddir},
$self->{meanwindsp},
$self->{gust},
$self->{windunit},
$self->{vis},
$self->{wx},
$self->{cloud},
$self->{supp},
$self->{change},
$self->{temps}
)

= ($metline =~ /
(?:$tafre)?\s*
($timestampre)?\s* # optional
timestamp
($icaore)\s* # mand icao
identifier
($latere)?\s*
($issuere)?\s* # optional issue
time
($periodre)\s* # Mandatory period
(?:$windre)?\s* # wind, gust
# ($onevisre\s*)?\s* # a vis gps
((?:$onevisre\s*)*)\s* # zero or more vis
gps
((?:$onewxre\s*)*)\s* # zero or more wx
groups
($cloudre)?\s* # optional cloud
group
((?:$onesuppre\s*)*\s*)
(.*?)\s*
((?:$onetaftempre\s*)*\s*)=/sx))
# supplementary info