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.
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? |