There'll need to be a change to DBD::Pg before \? will work. I've opened a case for it https://rt.cpan.org/Ticket/Display.html?id=101030
Once implemented you'd use the usual SQL::Abstract way of specifying operators: https://metacpan.org/pod/SQL::Abstract#Specific-comparison-operators Tim. On Fri, Dec 19, 2014 at 09:55:42AM -0800, Augustus Saunders wrote: > Tim, thanks for the detailed response. One other poster had suggested I use > the dollar only placeholder setting, but as you pointed out, DBIx::Class (I > guess due to SQL::Abstract) is using ?, so I got errors about mixing the two. > I'm happy to contribute any discussion on the ramifications, but if you could > tell me where in the DBIx::Class code I to look, then we could try \? here. > Thanks! > > Augustus > > On Dec 19, 2014, at 2:40 AM, Tim Bunce <tim.bu...@pobox.com> wrote: > > > Hello Augustus. > > > > On Thu, Dec 04, 2014 at 03:38:14PM -0800, Augustus Saunders wrote: > >> Hi all, I have been unable to find a way to use some of the new > >> JSONB operators in Postgres 9.4 with DBIx::Class. A quick search > >> for JSONB on the email archive site didn't turn up any results, so > >> I thought I would ask. > >> > >> In particular, ?, ?&, and ?| are now operators, and we run into > >> problems with the DBI placeholder being ?. > > > > As more people start using Postgres 9.4 and the JSON operators this > > is going to become a significant problem. > > > >> I read that putting single quotes around the question mark would > >> allow a literal question mark in DBI, but I can't seem to make this > >> work from DBIx::Class. > > > > It won't do what you want. Question marks in quotes are ignored by the > > DBI driver, but question marks in quotes won't work as JSON operators. > > > >> Can anybody tell me whether this is currently possible, if so how, > >> and if not what might be involved or where in the code to look? > > > > For code not using DBIx::Class the pg_placeholder_dollaronly attribute > > might work, see https://metacpan.org/pod/DBD::Pg#Placeholders > > > > For code using DBIx::Class the problem is more tricky. I'm pretty sure > > that SQL::Abstract and thus DBIx::Class only support question mark > > placeholders. That means it probably impossible to use expressions > > containing a question mark operator with SQL::Abstract/DBIx::Class. > > (Though I'd be delighted to be proven wrong.) > > > > So I think the DBI spec for placeholders needs to be extended to allow a > > way to 'escape' a question mark that the driver would otherwise treat as > > a placeholder. > > > > The obvious and natural approach would be to use a backslash before a > > question mark. The backslash would be removed by the driver before the > > statement is passed to the backend. > > > > $dbh->selectrow_array(q{ SELECT {"a":1, "b":2}'::jsonb ? 'b' }); # breaks > > $dbh->selectrow_array(q{ SELECT {"a":1, "b":2}'::jsonb \? 'b' }); # would > > work > > > > The key question is: what is the risk of any existing DBI SQL statements > > containing a question mark placeholder that's preceeded by a backslash? > > > > Can anyone think of realistic examples? (For ANY DBI driver or backend.) > > > > Tim. > > > > _______________________________________________ > > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > > IRC: irc.perl.org#dbix-class > > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > > Searchable Archive: > > http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk > > > _______________________________________________ > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > IRC: irc.perl.org#dbix-class > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk