Re: [Haskell-cafe] Takusen, postgres and boolean fields

When I did SELECT statement I handled boolean field as String, and convert it to Bool. However when I did update or insert, I must bind those values, then takusen calls foreign postgres library and function with "?" and values of proper type. So I cannot use neither Bool neither String in bindP. Instead of binding, I've used ugly hack : "update some_table set some_boolean_field = ? ..." [bindP True, ...] is replaced with (printf "update some_table set some_boolean_field = '%s' ... " "t") [...] I really hate it and I hope there is better way to do it. On Sat, Apr 25, 2009 at 2:06 PM, Christoph Bauer wrote:

Sasha Shipka writes:

...

Let say one has to do something similar to this:

execDML $ cmdbind (sql "update some_table set some_boolean_field = ? where ...") [bindP True, ...]

When I do it, I have an error:

DBError ("42","804") 7 "ERROR:  42804: column \"some_boolean_field\" is of type boolean but expression is of type text ..."

I've noticed that when I read boolean fields from postgres it reads them as string "t" or "f". So I also tried bindP "t" and had same error.

Indeed, I also have such problems in my application [1] in   SELECT boolean_field from TABLE.

My workaround is: I defined a plpgsql function

CREATE OR REPLACE FUNCTION HBoolean(v IN BOOLEAN) RETURNS TEXT AS $$ BEGIN   IF v THEN RETURN 'True';   ELSE RETURN 'False';   END IF; END; $$ LANGUAGE plpgsql;

and rewrite my query as

  SELECT HBoolean(boolean_field) from TABLE

and  takusen converts it to Bool.

For performance reason you may convert from text to boolean (but keep bindP True). If there is a better solution, I'm also glad to know it.

Christoph Bauer

[1] http://www.communitystory.de