inserting composite types

Hi, 

Im using  composite types in a table and DBIx::Class to access my data, yet I believe the problem is not  DBIx::Class  related.
My  problem:

*  I cannot insert null values in a "column' of a nested composite type.

So I have a table with  two composite types, one of these (pdf_template) has a column tht itself is a composite type:
#\d pdf_element

 Spalte |          Typ          
--------+-----------------------
 pos_x  | real
 pos_y  | real
 width  | real
 height | real
 pdfpos | character varying(50)

# \d pdf_template
 Spalte |          Typ           
--------+------------------------
 name   | character varying(100)
 start  | integer
 step   | integer
 pos    | pdf_element

And the table
 \d pdf_format_param
    Spalte     |        Typ        |                             Attribute                             
---------------+-------------------+-------------------------------------------------------------------
 id            | integer           | not null Vorgabewert nextval('pdf_format_param_id_seq'::regclass)
 dim_x         | real              | 
 dim_y         | real              | 
 bg_tpl        | pdf_template      | 
 logo          | pdf_element       | 
….


So, with psql I can do:

INSERT INTO pdf_format_param ( bg_tpl, logo, id) values (('xx.pdf',1,2,null), (10,20,30,40,null),10000);
INSERT 0 1
pgp=# select  bg_tpl,logo from pdf_format_param where id = 10000; 
    bg_tpl     |     logo      
---------------+----------------
 (xx.pdf,1,2,) | (10,20,30,40,)
(1 Zeile)

So, inserting a null value in column  pdfpos   of pdf_element logo and a null value for column  pos in pdf_template bg_tpl , which itself is a pdf_element, works fine.

But how do I do this with bind  values ? 

my $pp = $s->resultset('PdfFormatParam')->create({id=>10000,
                                                  image  => '(1,2,3,4,"abc")',
                                                  });
works, yet 
my $pp = $s->resultset('PdfFormatParam')->create({id=>10000, 	
                                                  image  => '(1,2,3,4,undef)',
                                                  });
 gives me:
select  bg_tpl, image from pdf_format_param where id = 10000; 
 bg_tpl |      image      
--------+-----------------
        | (1,2,3,4,undef)

while
my $pp = $s->resultset('PdfFormatParam')->create({id=>10000,
                                                  image  => '(1,2,3,4,)',
                                                  });
works fine and  gives me:
select  bg_tpl, image from pdf_format_param where id = 10000; select  bg_tpl, image from pdf_format_param where id = 10000 and (image).pdfpos is null; 
 bg_tpl |      image      
--------+-----------------
        | (1,2,3,4,)


which  is Ok. 

But I haven't found a solution to insert a row with  a null value for pdf_template.pos :
INSERT NON NULL VALUES WORKS: 
my $pp = $s->resultset('PdfFormatParam')->create({id=>10000,
                                                  image  => "(1,2,3,4,)",
                                                  bg_tpl => '(xx.pdf, 1,2, "(0,1,2,3,abc)" )',
                                                  });

AND 
my $pp = $s->resultset('PdfFormatParam')->create({id=>10000,
                                                  image  => "(1,2,3,4,)",
                                                  bg_tpl => '(xx.pdf, 1,2, "(0,1,2,3,)" )',
                                                  });

both work, but setting the whole "column pos to null doesn't:

my $pp = $s->resultset('PdfFormatParam')->create({id=>10000,
                                                  image  => "(1,2,3,4,)",
                                                  bg_tpl => '("xx.pdf", 1,2, )',
                                                  });

gives me ( since my errors are in german I had to translate them, so the original text may differ!)

DBIx::Class::ResultSet::create(): DBI Exception: DBD::Pg::st execute failed: FEHLER:  fehlerhafte Record-Konstante: » «
DETAIL:  Left bracket missinng. [for Statement "INSERT INTO pdf_format_param ( bg_tpl, id, image) VALUES ( ?, ?, ? )" with ParamValues: 1='("xx.pdf", 1,2, )', 2='10000', 3='(1,2,3,4,)'] at /home/rs/perl/xx.pl line 28

Next try:
my $pp = $s->resultset('PdfFormatParam')->create({id=>10000,
                                                  image  => "(1,2,3,4,)",
                                                  bg_tpl => '(xx.pdf, 1,2,() )',
                                                  });

gives 

DBIx::Class::ResultSet::create(): DBI Exception: DBD::Pg::st execute failed: FEHLER:  fehlerhafte Record-Konstante: »(«
DETAIL:  Unecpected end of input. [for Statement "INSERT INTO pdf_format_param ( bg_tpl, id, image) VALUES ( ?, ?, ? )" with ParamValues: 1='(xx.pdf, 1,2,() )', 2='10000', 3='(1,2,3,4,)'] at /home/rs/perl/xx.pl line 28

How do I have to quote this to get this working?


Rolf Schaufelberger