Re: Migration from INFORMIX to POSTGRESQL

On 2010-02-24, Atif Jung <atifjung@gmail.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.