Re: [Haskell-cafe] Takusen, postgres and boolean fields
Sasha Shipka xao...@gmail.com 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 ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
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 c-bauer-olsbruec...@t-online.de wrote: Sasha Shipka xao...@gmail.com 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 ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Takusen, postgres and boolean fields
2009/4/25 Sasha Shipka xao...@gmail.com: 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. It should be pretty simple to add Bool instances to the class that handles bind variable marshalling. I'll look into it on Monday. As a workaround for now, can you use the CAST sql function to convert text to bool values in Postgres? Alistair ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
