Re: Migration from INFORMIX to POSTGRESQL

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Migration from INFORMIX to POSTGRESQL
Date: 2010-02-25 09:26:18
Message-ID: hm5fnq$b0l$1@reversiblemaps.ath.cx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On 2010-02-24, Atif Jung <atifjung(at)gmail(dot)com> wrote:
> --001485f44d1849d0350480575ea7
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi,
>
> I've recently started on a project whereby we are migrating our INFORMIX 9.2
> database to POSTGRES 8.4.2. We are using ESQL (Embedded SQL) in a C language
> environment running on AIX 6.1. I have a question regarding the following
> problem.
>
> The INFORMIX code reads as follows, please note that acHostFormat has
> already been set prior to the call below:
>
> EXEC SQL DECLARE cursName CURSOR FOR
> SELECT code, priority INTO :acCode, :acPriority
> FROM name_link
> *WHERE :acHostFormat MATCHES pattern*
> ORDER BY priority;
>
> Now I am aware that POSTGRES does not have the *MATCHES* clause but uses the
> *IN* clause instead. My POSTGRES version looks like:

in not right, you'll have to re-write your patterns into a format that
postgres understands.

SQL regular expression using the SIMILAR TO operator
POSIX extended regular expression using the ~ operator
SQL like using the LIKE operator

If your patterns column contain no values with any character among these:

% _ + ( ) |

then SIMILAR TO is probably the way to go

just change '*' to '%' and '?' to '_'

> *WHERE :acHostFormat IN pattern*

becomes

WHERE :acHostFromat SIMILAR TO replace(replace(pattern,'*','%'),'?','_')

it may be worthwhile to make the changes to the table itself.

UPDATE forgotwhatitwascalled SET pattern=replace(replace(pattern,'*','%'),'?','_');

then just use SIMILAR TO in place of MATCHES.

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Louis Becker 2010-02-25 11:04:00 Importing lines of variable length from ASCII
Previous Message Alex Hunsaker 2010-02-24 23:46:32 Re: How to get the permissions assigned to user?