Thread: Migration from INFORMIX to POSTGRESQL

Atif Jung, 24.02.2010 12:56:
> The problem I'm having is that on compilation I get the following error:
> ERROR: syntax error at or near "pattern".
> The pattern column in table name_link has entries like:
> " *I[0-Z] [0-Z] [0-Z] [0-9]* "
> Any help and advice would be greatly appreciated.
> Thank you.

IN will require a list of values supplied (e.g. IN (1,2,3) or IN ('one', 'two', 'three).

It has nothing to do with regular expression.

You want to either use the regexp_matches() function or the MATCHES operator:
http://www.postgresql.org/docs/current/static/functions-matching.html

So something like:

SELECT code, priority INTO :acCode, :acPriority
FROM name_link
WHERE regexp_matches(:acHostFormat, pattern)
ORDER BY priority;

Thomas

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.