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