The following bug has been logged online:

Bug reference: 1324
Logged by: Tzvetan Tzankov

Email address: tzankov (AT) noxis (DOT) net

PostgreSQL version: 8.0 Beta

Operating system: Debian

Description: create domain strange behaviour

Details:

I will post a sort test

test=# create schema test;
CREATE SCHEMA
test=# set search_path to test;
SET
test=# CREATE DOMAIN session_id AS character(9) CONSTRAINT
ch_session_id_field CHECK (value ~ '[0-9]{4}-[0-9]{4}-[0-9]{4}-[0-9]{4}');
CREATE DOMAIN
test=#
test=# CREATE OR REPLACE FUNCTION generate_session_id() RETURNS session_id
AS $$
test$# begin
test$# return lpad(text(floor(random()*10000)), 4, '0') || '-' ||
lpad(text(floor(random()*10000)), 4, '0') || '-' ||
lpad(text(floor(random()*10000)), 4, '0') || '-' ||
lpad(text(floor(random()*10000)), 4, '0');
test$# end;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
test=# CREATE TABLE session (
test(# id session_id NOT NULL default generate_session_id() PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"session_pkey" for table "session"
CREATE TABLE
test=# insert into session default values;
INSERT 2272280 1
test=# select * from session;
id
---------------------
3199-4274-8097-7843
(1 row)

test=# insert into session values ('3199-4274-8097-7842');
ERROR: value too long for type character(9)
test=# select version();
version
---------------------------------------------------------------------------
----------------------
PostgreSQL 8.0.0beta4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4
(Debian 1:3.3.4-13)
(1 row)


well this was first of all, a bug in my domain creating statement, which I
should have done character(19) insetead of 9, but instead of generating
error on default value it accepted it (when I try to insert a random value
by hand it gives however an error)


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
IIRC, plpgsql is not yet domain-aware and won't check constraints
associated with domain values. I'd suggest declaring
generate_session_id() to return plain text, so that the domain coercion
and constraint test happen after the function returns.

Yes, this is a bug in plpgsql, but it's not going to get fixed right
away.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match