It can be done but i don't think you can do it pure SQL unless you want to
start using substr and all that.
You can useone of the built in languages such as pltcl or plperl.
These will help you achieve what you want.
Quick pltcl one
CREATE OR REPLACE FUNCTION sp_reports() RETURNS BOOLEAN AS '
spi_exec -array C "SELECT path FROM page WHERE path LIKE
''/academic-staff/reports/%''" {
foreach element [split $C(path) "/"] {
if { $element == "reports" } {
set element "fooreports"
}
append newpath "$element/"
}
lappend newpath_list [list "$C(path)" "$newpath"]
}
foreach element $newpath_list {
foreach { oldpath newpath } $element {}
spi_exec "UPDATE page SET path = ''$newpath'' WHERE path =
''$oldpath''"
}
RETURN 1
' LANGUAGE 'pltcl';
This is just a quick and dirty way to do it and i haven't even tested it
but it should work fine
HTH
Darren Ferguson
On Mon, 17 Jun 2002, David A Dickson wrote:
> Sorry, messed up the subject.
>
> On Mon, 17 Jun 2002, David A Dickson wrote:
>
> I am trying to do an update using regular expressions. Is something
> like this possible?
>
> a=# select path from page where path ~ '/academic-staff/reports/(.*$)';
> path
> --------------------------------------
> /academic-staff/reports/agriculture/
> /academic-staff/reports/arts/
> /academic-staff/reports/education/
> /academic-staff/reports/engineering/
> /academic-staff/reports/medicine/
> /academic-staff/reports/music/
> /academic-staff/reports/science/
> /academic-staff/reports/
>
> a=# update page set path = '/academic-staff/fooreports/\1'
> where path ~ '/academic-staff/reports/(.*$)';
>
>
> What I'm trying to do is replace all occurances of
> /academic-staff/reports/(.*$) with
> /academic-staff/fooreports/\1
> where \1 is everything that was matched by the .*$
>
> can it be done? anyone know how?
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
