Subject Re: [firebird-support] Regular Expressions & Domains
Author Helen Borrie
At 12:16 PM 24/06/2003 +0000, you wrote:
>I think I might be advised to take this to a development forum, but
>here goes anyway....
>I have seen from the "Open Requests" list at Sourceforge that a
>reques was made some time ago for the use of regexps in the "Where"
>clause of a select statement.
>However, I was wondering if regexps are usable now. I found this
>document on the IBPhonix site:
>It talks about the aim to have UDF library in Interbase 5.0 that
>would (or might) include a regexp match function.
>My question is a) does such a function exist in any available UDF
>library (I cannot find it),


>b) could such a UDF be used in the
>definition of a Domain?

If at all, only in a CHECK constraint. But I haven't tested to see whether
something like CHECK (VALUE = lower(VALUE)) actually works.

>It appears to me that this would be a useful thing. My thinking is
>that it would allow for a very precise but flexible mechanism for
>defining domains.

Hmm, in what way would you use it? In a domain, you can prescribe
attributes of incoming values for validation, but you can't use the domain
to change a value. Provided you discover that a CHECK constraint can
process a UDF call, you could have VALUE checked against some regex

What I yearn for is a nice little UDF-ish library that processes RegExs,
which I would use in triggers. Now - domain triggers - there's a superb idea.

>Furthermore, if the definition could itself be
>retrieved from the database it could be used in any programming
>languages that supported the same school of regexps, thus providing a
>single point of control for valid values as data was entered on the

That's why I think a regex processor needs its own library (or
libraries). Another "branch" in the UDF paradigm, along with blob filters
and collations. I was exploring the possibility of doing it at one stage,
in Object Pascal, so it could be maintained as a single codebase for both
Linux and Windows. That doesn't provide a blanket solution for all
supported platforms though.

>I held back in the past from making such a suggestion because I
>realised that it would probably not be compliant with the SQL-92 etc
>standards. However, I seem to remember a recent discussion in the
>groups here that it was stated that Firebird can have additional
>features which are not in SQL 92 if they are useful to users.

UDFs, blob filters, collations are all extensions that the standard doesn't
cover. There's a lot of confusion about the standard. It doesn't prevent
you from implementing a feature - it only requires you to surface a
standard feature in a specific way.

>I don't mind being told to take this request to the development forum
>(is that on Yahoo?), but I would like to know if there is such a UDF

There might be - you can catch up with the latest UDF contributions at the
ibphoenix website (Downloads > contributed > UDFs)

>and if the UDF could be used to define a domain.

It's a development idea worth discussion but I think you'd need to be
clearer about what you wanted to achieve. Breaking the current
implementation of domains is a matter for an ODS change. A better place to
raise it is in firebird-Architect (where a clear exposition gets a more
receptive response than anything this vague).

Dealing with it via triggers doesn't require a violation of domain
standards and it's a more robust approach, IMO - a question of expanding an
existing paradigm.

Domain triggers is an interesting idea - it's moving towards
object-relational when you can build into a domain the ability for its
members (wherever they live in the database) to alter their own inputs...

> From my reading of
>the documentation a domain definition couldn't use a UDF

You could test it in a CHECK constraint.
But you certainly can't do
create domain blatt as (ManipulateMe(varchar(30)), since UDFs must take
constants as arguments, amongst other problems.

>, so I think
>this might well have to be made into a feature request.

Possibly, but the more well-considered a feature request, the more likely
someone will get interested in doing it.