Re: 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