Thread

1. BUG #1324: create domain strange behaviour

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-11-17T15:02:41Z

   The following bug has been logged online:
   
   Bug reference:      1324
   Logged by:          Tzvetan Tzankov
   
   Email address:      tzankov@noxis.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) 
   
   
   

2. Re: BUG #1324: create domain strange behaviour

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-17T15:44:35Z

   "PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
   > Description:        create domain strange behaviour
   
   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