Dan Lyke <danlyke@flutterby.com> writes:
> 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'.
There shouldn't be any need to convert newlines into \n in quoted
strings; either way should work.
> 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 would call this a clear bug in psql. I cannot replicate the loss of
leading whitespace in current sources, but I do see the loss of the
empty line. Turning on debug logging shows that the empty line has
been stripped by psql, not the backend --- it's not there in the query
that arrives at the backend.
I tried a similar experiment in pgtclsh and didn't see any loss of
blank lines, so the problem seems to be specific to psql. Your
observation in DBI must be an independent bug in either DBD::Pg or
DBI itself.
> 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).
The Postgres parser is not broken, AFAICT. If you can find where this
is happening in the DBI/DBD path, please submit a patch.
regards, tom lane