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.
