Задался целью проверять данные, вводимые в базу через веб-форму на предмет отсутствия в полях данных недопустимых символов. Например, есть поля для хранения имени и фамилии, в них, соответственно, не должно быть ничего, кроме русских букв, пробелов и дефиса. Для этого написал такую вот монстроподобную функцию на pl/perl и триггер к ней:

create function check_users() returns trigger as $$
my $errstr = "";
if ( !($_TD->{new}{lastname} =~ /^[^A-Za-z0-9\!\@\#\$\%\^\&\*\(\)\_\+\=\\\/\?\,\.\<\>\~\`\"\'\;\:\{\}\[\]]+$/) ) {
$errstr.= "<br>\nCHECK_USERS: Недопустимый символ в поле ФАМИЛИЯ";
}
if ( !($_TD->{new}{firstname} =~ /^[^A-Za-z0-9\!\@\#\$\%\^\&\*\(\)\_\+\=\\\/\?\,\.\<\>\~\`\"\'\;\:\{\}\[\]]+$/) ) {
$errstr.= "<br>\nCHECK_USERS: Недопустимый символ в поле ИМЯ";
}
if ( $errstr ne '' ) {
elog(ERROR, $errstr);
return "SKIP";
}
else { return; }
$$ language 'plperl';

create trigger tg_check_users before insert or update on users
for each row execute procedure check_users();

Проблема в том, что используемые регэкспы не желают отлавливать одинарные кавычки, слеши и некоторые другие спецсимволы при выполнении этой функции - хотя будучи использованы в приложении на perl, эти регэкспы отрабатывают как надо и не пропускают ничего, кроме руских буковок.
Вот собственно и вопрос - что предпринять для того, чтобы данная функция заработала как надо, или каким еще способом можно осуществлять такого рода проверки?

ilejnОтвета на поставленный вопрос я не знаю, но происходит изобретение велосипеда.
Мда, действительно. Спасибо за наводку.

ilejnв сочетании с SIMILAR TO легко решит поставленную задачу безо всяких перлов и триггеров.
Оно конечно проще, но своей цели я не добился - спецсимволы по-прежнему просачиваются:

Создал тестовую табличку createtestbase.sql:

create table test (
uid serial8 primary key,
lastname varchar(64) not null,
firstname varchar(64) not null,
constraint empty_field
check ( lastname <> '' and firstname <> ''),
constraint valid_chars
check ( lastname not similar to '^[A-Za-z0-9\\\']+$')
);

insert into test (lastname, firstname) values ('Smith', 'Jonh');
insert into test (lastname, firstname) values ('Петров', 'Иван');
insert into test (lastname, firstname) values ('Пе\тров', 'Ив\'ан');

Проверяем:

# su -l pgsql -c "exec /usr/local/bin/psql testbase < createtestbase.sql"
CREATE DATABASE
NOTICE: CREATE TABLE will create implicit sequence "test_uid_seq" for serial column "test.uid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
ERROR: new row for relation "test" violates check constraint "valid_chars"
INSERT 0 1
INSERT 0 1

Вставились две записи вместо одной :(

# su -l pgsql -c "exec /usr/local/bin/psql testbase"

Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

testbase=# select * from test;
uid | lastname | firstname
-----+----------+-----------
2 | Петров | Иван
3 | Петров | Ив'ан
(записей: 2)

testbase=# \q

Вопрос по-прежнему открыт - можно ли заблокировать ввод пользователем слешей, кавычек и прочих служебных символов и каким образом это делать, кроме как в самом приложении-клиенте?

ilejnТакой вариант

create table const_check
(
f text
check (f similar to '[А-Яа-я -]+')
)

не рассматривался?

Если используется UNICODE, все будет работать.

Рассматривался, но используется KOI8-R, и в ближайшее воемя перейти на юникод не получится

JelisДа и почему на уникод не получиться перейти? Клиета то можно оставить как и было, в КОИ8, а базу переделать под уникод.
Конвертацию в проверке я обязательно попробую, а перейти не получится по причине моих ограниченных знаний и прав для работы с БД. Да и если бы были права, не хотелось бы невзначай убить неосторожным движением рабочие базы, я с Postgresql очень недавно дело имею.

Jelis А может тогда попробовать вначале приводить к UTF8, специально для проверки. Примерно так

Сделал по твоему совету:

create table test (
uid serial8 primary key,
lastname varchar(64) not null,
firstname varchar(64) not null,
constraint empty_field
check ( lastname <> '' and firstname <> ''),
constraint valid_chars
check (convert(lastname , 'koi8', 'utf8') similar to '[А-Яа-я -]+')
);

insert into test (lastname, firstname) values ('Smith', 'Jonh');
insert into test (lastname, firstname) values ('Петров', 'Иван');
insert into test (lastname, firstname) values ('Пе\тров', 'Ив\'ан');

увы, не работает

CREATE DATABASE
NOTICE: CREATE TABLE will create implicit sequence "test_uid_seq" for serial column "test.uid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
ERROR: new row for relation "test" violates check constraint "valid_chars"
ERROR: new row for relation "test" violates check constraint "valid_chars"
ERROR: new row for relation "test" violates check constraint "valid_chars"

ilejnНет ничего удивительного: cтрока в UTF, а паттерн в KOI-8.
Блин.
В общем, как я понял, пока база в кодировке KOI, решения не существует?

ффффЧто-то вас не в ту степь понесло. При чём тут юникод?
\w должен давать совпадение с буквами в любой однобайтной кодировке (а вот в юникоде - наоборот не работает).

в поле lastname по моему замыслу допустимы только РУССКИЕ буквы, пробел и дефис, так что \w никак не поможет

ilejnЧто мешает сконвертировать в UTF и pattern тоже?
Либо мои кривые руки, либо BSD не дружит с юникодом. На FreeBSD 6.1 пробую так:
# iconv -f koi8-r -t utf8 1.txt
iconv: conversion to utf8 unsupported
ilejnВообще-то, мне непонятно, почему это не работает в KOI-8 само по себе. Казалось бы, кодировка официально поддерживается, сортировка по ней есть, а вот сделать regexp поленились, получается? Странно это.
Очень странно, действительно
ilejnЧто возвращает show lc_collate на Вашей базе?
testbase=# show lc_collate;
lc_collate
--------------
ru_RU.KOI8-R
(1 запись)

testbase=#

ффффУсловия на проверку одним регэкспом тоже нет

Кавычки и слеши по-прежнему проскакивают сквозь этот регэксп.
Начинаю подозревать, что запретить их на уровне сервера БД вообще невозможно

ilejnВы же используете PostgreSQL'ный convert для строки. Используйте его же для образца.
Это я уже пробовал, резуультат тот же.
Пишу

check (convert(lastname , 'koi8', 'utf8') similar to convert('[А-Яа-я -]+', 'koi8', 'utf8'))

в ответ на

insert into test (lastname, firstname) values ('Smith', 'Jonh');
insert into test (lastname, firstname) values ('Петров', 'Иван');
insert into test (lastname, firstname) values ('Пе\тров', 'Ив\'ан');

получал

ERROR: new row for relation "test" violates check constraint "valid_chars"
ERROR: new row for relation "test" violates check constraint "valid_chars"
ERROR: new row for relation "test" violates check constraint "valid_chars"

glebofff1. наверняка проблемы с кодировкой у клиента
А если клиент - штатный psql?
С утра на виртуалке поднял PostgreSQL и в нем ковыряюсь. Сортировки/поиск/upper/lower работают. Работают, в общем-то и рэгэкспы, одно НО - они пропускают символы "\" и "'" (обратный слэш и одинарная кавычка)
Слушайте, может, я зря вообще парюсь? Может не так уж и опасно допускать ввод этих символов?

ilejnнекультурное - отказаться от диапазонов и перечислить все русские буковки one by one в квадратных скобках.
Есть и третье решение - делать эти проверки в клиенте, благо в перловой CGIшке все это работает на ура. Но вот в целях повышения самообразованности задался целью все проверки/правила организовать именно на сервере... хотя может и зря :-\