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
