String manipulation

On Tue, Feb 17, 2004 at 03:46:53 -0800,
pativo <pativo@arcor.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:

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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pativo@arcor.de (pativo) wrote in message news:<58babf1b.0402170346.4a719868@posting.google.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