If, using DBI with DBD::Pg, you call
$dbh->do('INSERT INTO xyz(stuff) VALUES(' $dbh->quote("a string with \n\n a two line break").')');
You'll get a string with a single line break back, because DBD::Pg
doesn't escape newlines into '\n'.
This is consistent with the interface via psql:
test=# insert into xyz(stuff) values('a string with
test'#
test'# a two line break');
test=# select stuff from xyz; stuff
----------------------------------a string with a two line break
(1 row)
(As you can see there are also some interesting issues with leading
spaces going on here).
I don't have a copy of the SQL standard in front of me, so I'm not
sure whether this needs to be fixed in the PostgreSQL parser (seems
like the logical place, but we're into minutae of compatibility) or
DBD::Pg (the easy place, but maybe this is a hack that's going to have
to go in every interface).
I also haven't tested with putting the string inside the
$sth->execute(...) call, and haven't dug through enough of the DBI
code to know how exactly that gets passed to the underlying driver.
Anyway, I've got over 4000 records that need to get fixed by hand
because of this, and want to make sure nobody else gets bitten, and
would like to implement a solution that doesn't require me remembering
to patch things every time I upgrade.
Dan
