FAQ
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

Search Discussions

  • David E. Wheeler at Nov 10, 2010 at 5:50 pm

    On Nov 10, 2010, at 9:42 AM, Rolf Schaufelberger wrote:

    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?
    Try

    image => "(1,2,3,4,NULL)"

    Really, though, you should be using ROW() with placeholders.

    $dbh->do(q{
    INSERT INTO whatever VALUES (ROW(?, ?, ?, ?, ?)::image)
    }, undef, 1, 2, 3, 4, undef);

    But your ORM isn't gonna do that.

    Best,

    David

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbd-pg @
categoriesperl
postedNov 10, '10 at 5:42p
activeNov 10, '10 at 5:50p
posts2
users2
websiteperl.org

People

Translate

site design / logo © 2022 Grokbase