Re: String manipulation

Hello to all,

I have small problem. I have some database fields (VARCHAR)
and these field should hold some ahex coded values. So the
string length must be even and each character can only be
0-9, a-f or A-F.
My idea was that:

====8<-----------------------------------

CREATE TABLE test (
id INT4 PRIMARY KEY NOT NULL DEFAULT nextID(),
text VARCHAR(150) NOT NULL CHECK(isAHex(text))
);

CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
text_p ALIAS FOR $1;
BEGIN
IF ((length(text_p) % 2) <> 0) THEN
RETURN FALSE;
END IF;
-- TODO How to check each character
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

====8<-----------------------------------

Has anybody an idea how could I check each character?
I would prefer a solution in plpgsql!!

Thank!

pativo

--
\\://
(- 0)
---ooO-(_)-Ooo---

On Tue, Feb 17, 2004 at 03:46:53 -0800,
pativo <pativo(at)arcor(dot)de> wrote:
> Hello to all,
>
> I have small problem. I have some database fields (VARCHAR)
> and these field should hold some ahex coded values. So the
> string length must be even and each character can only be
> 0-9, a-f or A-F.

You can use a constraint that checks the value versus a regular
expression. Something like: ~ '^([0-9a-fA-F][0-9a-fA-F])+$^'
Unless the 150 character limit is a real business rule, you probably
want to use TEXT instead of VARCHAR(150).

You should be able to do that with a regular expression.

CHECK (text ~ '^([0-9A-F]{2})+$')

Remark: As the column is NOT NULL, I suppose that an empty string is not
valid. If an empty string must be valid, replace the + with * ;-)

pativo wrote:
> Hello to all,
>
> I have small problem. I have some database fields (VARCHAR)
> and these field should hold some ahex coded values. So the
> string length must be even and each character can only be
> 0-9, a-f or A-F.
> My idea was that:
>
> ====8<-----------------------------------
>
> CREATE TABLE test (
> id INT4 PRIMARY KEY NOT NULL DEFAULT nextID(),
> text VARCHAR(150) NOT NULL CHECK(isAHex(text))
> );
>
> CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
> DECLARE
> text_p ALIAS FOR $1;
> BEGIN
> IF ((length(text_p) % 2) <> 0) THEN
> RETURN FALSE;
> END IF;
> -- TODO How to check each character
> RETURN TRUE;
> END;
> ' LANGUAGE 'plpgsql';
>
> ====8<-----------------------------------
>
> Has anybody an idea how could I check each character?
> I would prefer a solution in plpgsql!!
>
> Thank!
>
>
> pativo
>

pativo wrote:

>Hello to all,
>
>I have small problem. I have some database fields (VARCHAR)
>and these field should hold some ahex coded values. So the
>string length must be even and each character can only be
>0-9, a-f or A-F.
>My idea was that:
>
>====8<-----------------------------------
>
>CREATE TABLE test (
> id INT4 PRIMARY KEY NOT NULL DEFAULT nextID(),
> text VARCHAR(150) NOT NULL CHECK(isAHex(text))
>);
>
>CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
> DECLARE
> text_p ALIAS FOR $1;
> BEGIN
> IF ((length(text_p) % 2) <> 0) THEN
> RETURN FALSE;
> END IF;
> -- TODO How to check each character
> RETURN TRUE;
> END;
>' LANGUAGE 'plpgsql';
>
>====8<-----------------------------------
>
>Has anybody an idea how could I check each character?
>I would prefer a solution in plpgsql!!
>
>Thank!
>
>
>pativo
>
>
>
Here's one solution:
CREATE OR REPLACE FUNCTION isAHex(text) RETURNS boolean as '

DECLARE
inputText ALIAS FOR $1;
tempChar text;
isHex boolean;
BEGIN
isHex = true;
IF ((length(inputText) % 2) <> 0) THEN
return FALSE;
END IF;
FOR i IN 1..length(inputText) LOOP
tempChar := substr(inputText, i, 1);
IF tempChar ~ ''[g-z]'' THEN
return FALSE;
ELSE IF tempChar ~ ''[G-Z]'' THEN
return FALSE;
END IF;
END LOOP;

return isHex;
END;
' LANGUAGE 'plpgsql';

You may have to check the IF...ELSE IF... stuff but this should work.
I've used a very similar one to check if a value is numeric.

Ron

Pascal Polleunus wrote:
> You should be able to do that with a regular expression.
>
> CHECK (text ~ '^([0-9A-F]{2})+$')
>
> Remark: As the column is NOT NULL, I suppose that an empty string is not
> valid. If an empty string must be valid, replace the + with * ;-)

I just noticed an unusual affect. GUC regex_flavor affects CHECK
constraints even after the check constraint has been created:

test=> SET regex_flavor = 'advanced'; -- default
SET
?column?
----------
t
(1 row)

test=> SET regex_flavor = 'basic';
SET
test=> SELECT 'abc' ~ '(a|x).*';
?column?
----------
f
(1 row)

test=> SET regex_flavor = 'advanced';
SET
test=> CREATE TABLE test (x TEXT CHECK (x ~ '(a|x).*'));
CREATE TABLE
test=> INSERT INTO test VALUES ('a');
INSERT 17149 1
test=> SET regex_flavor = 'basic';
SET
test=> INSERT INTO test VALUES ('a');
ERROR: new row for relation "test" violates check constraint "test_x"

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I just noticed an unusual affect. GUC regex_flavor affects CHECK
> constraints even after the check constraint has been created:

Why does that surprise you?

regards, tom lane

\Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I just noticed an unusual affect. GUC regex_flavor affects CHECK
> > constraints even after the check constraint has been created:
>
> Why does that surprise you?

I don't think it is good practice for a CHECK constraint to change its
behavior based on a GUC variable. I am not surprised, but am pointing
out it isn't ideal.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I don't think it is good practice for a CHECK constraint to change its
> behavior based on a GUC variable.

You can develop comparable "failure scenarios" for any of the GUC
variables that affect query semantics --- timezone, sql_inheritance,
you name it. Locking them all down when a check constraint or function
or view is created seems impractical ... and if we did do it then we'd
get complaints about that too. ("What do you mean I can't change the
setting later?")

In practice I think we have to assume that those variables are set
consistently within any one application. If you go frobbing them
on-the-fly then you're going to have issues.

I suppose paranoid sorts might lobby to make any GUC variable that can
change query semantics be a superuser-only setting, but to me that cure
sounds worse than the disease.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I don't think it is good practice for a CHECK constraint to change its
> > behavior based on a GUC variable.
>
> You can develop comparable "failure scenarios" for any of the GUC
> variables that affect query semantics --- timezone, sql_inheritance,
> you name it. Locking them all down when a check constraint or function
> or view is created seems impractical ... and if we did do it then we'd
> get complaints about that too. ("What do you mean I can't change the
> setting later?")
>
> In practice I think we have to assume that those variables are set
> consistently within any one application. If you go frobbing them
> on-the-fly then you're going to have issues.
>
> I suppose paranoid sorts might lobby to make any GUC variable that can
> change query semantics be a superuser-only setting, but to me that cure
> sounds worse than the disease.

What concerned me is that it would actually make data the passed the
CHECK constraint initially fail later. Look at this:

test=> CREATE TABLE test (x TEXT CHECK (x ~ '(a|x).*'), y INT);
CREATE TABLE
test=> INSERT INTO test VALUES ('a', 1);
INSERT 380556 1
test=> SET regex_flavor = 'basic';
SET
test=> UPDATE test SET y=2;
ERROR: new row for relation "test" violates check constraint "test_x"

The UPDATE fails even when the row isn't changed. Certainly interesting.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

pativo(at)arcor(dot)de (pativo) wrote in message news:<58babf1b(dot)0402170346(dot)4a719868(at)posting(dot)google(dot)com>...

Hello and thanks to all!

I'm amused that I get so many responses.

Ok, at time I use a plpgsql function (isAHex(VARCHAR)) which checks only
if the length is even.
So I think I should implement the following.

=======8<-------------------------------------------------------------------

CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
input_text_p ALIAS FOR $1;
tmp_char VARCHAR;
BEGIN
IF ((length(input_text_p) % 2) <> 0) THEN
RETURN FALSE;
END IF;
FOR i IN 1..length(input_text_p) LOOP
tmp_char := substr(input_text_p, i, 1);
IF NOT tmp_char ~ ''[0-9a-fA-F]'' THEN
RETURN FALSE;
END IF;
END LOOP;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE textsTest (
surrogate VARCHAR(40) PRIMARY KEY,
hex_text VARCHAR(150) NOT NULL CHECK(isAHexTest(hex_text))
);

=======8<-------------------------------------------------------------------

But for next release I will use the following. This is in my eyes
the better solution.

=======8<-------------------------------------------------------------------

CREATE TABLE textsTest (
surrogate VARCHAR(40) PRIMARY KEY,
hex_text TEXT NOT NULL CHECK(text ~ '^([0-9A-Fa-f]{2})+$')
);

=======8<-------------------------------------------------------------------

Thanks to all!!!

oki,

pativo

--
www.pativo.de