Re: [HACKERS] Binary data type with other output method
On Wed, 26 Dec 2007 20:20:59 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem. The answer is surely to fix the PHP driver rather than trying to mangle Postgres. The Perl DBD::Pg driver does not suffer this problem, so it can certainly be worked around (in fact in DBD::Pg you get a choice if 1/0 or t/f values for booleans). This PHP driver is in use since years, do you really expect they will fix this bug and make thousands of applications fail? Everyone like Mark who posted an example earlier would have to change the PHP code if the variable is now a true/false boolean instead a 't'/'f' string. No, i don't expect a bugfix here. Newer drivers like PDO seems to behave correct but this will not help if you cannot use this driver. As i told earlier, this is really a PHP bug, since libpq knows the type of every returned row in PQftype. DBD::Pg and PHP PDO seems to use this information. Anyway, this does not help in my case. My question was if there is a shorter way to create a boolean type with another output than to recreate all the casts, operators ect. If not, i have to go with this approach but maybe someone else has a more elegant idea. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: On Wed, 26 Dec 2007 20:20:59 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem. The answer is surely to fix the PHP driver rather than trying to mangle Postgres. The Perl DBD::Pg driver does not suffer this problem, so it can certainly be worked around (in fact in DBD::Pg you get a choice if 1/0 or t/f values for booleans). This PHP driver is in use since years, do you really expect they will fix this bug and make thousands of applications fail? Everyone like Mark who posted an example earlier would have to change the PHP code if the variable is now a true/false boolean instead a 't'/'f' string. No, i don't expect a bugfix here. Newer drivers like PDO seems to behave correct but this will not help if you cannot use this driver. I don't see why it couldn't be switchable behaviour, just as it is in DBD::Pg. Also, earlier you said: I think you'd really need a separate type. But have you considered something simple like CREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1) i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Binary data type with other output method
On Thu, 27 Dec 2007 08:52:15 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: On Wed, 26 Dec 2007 20:20:59 -0500 Andrew Dunstan wrote: This PHP driver is in use since years, do you really expect they will fix this bug and make thousands of applications fail? Everyone like Mark who posted an example earlier would have to change the PHP code if the variable is now a true/false boolean instead a 't'/'f' string. No, i don't expect a bugfix here. Newer drivers like PDO seems to behave correct but this will not help if you cannot use this driver. I don't see why it couldn't be switchable behaviour, just as it is in DBD::Pg. Maybe because the PHP folks don't care so much? I don't know. Already discussed this one on IRC but there's not so much love for fixing this issue, as far as i can see. Either i got: MySQL does this right in returning 0/1, so we don't care or i got other drivers fixed this problem, use this one. i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. Yes, but i will loose 't', 'true', 'f' and 'false', the usual input values in PostgreSQL beside '0' and '1'. It's not that the people don't use true/false, they use this a lot. But they expect boolean variable in PHP working like a bolean column in PG ... without casts, without extra workarounds in the code. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. Yes, but i will loose 't', 'true', 'f' and 'false', the usual input values in PostgreSQL beside '0' and '1'. It's not that the people don't use true/false, they use this a lot. But they expect boolean variable in PHP working like a bolean column in PG ... without casts, without extra workarounds in the code. If your code is prepared to emit t/f but not accept it then that seems to violate Postel's admonition: Be *liberal* in what you *accept*, and *conservative* in what you send. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Binary data type with other output method
On Thu, 27 Dec 2007 11:39:29 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. Yes, but i will loose 't', 'true', 'f' and 'false', the usual input values in PostgreSQL beside '0' and '1'. It's not that the people don't use true/false, they use this a lot. But they expect boolean variable in PHP working like a bolean column in PG ... without casts, without extra workarounds in the code. If your code is prepared to emit t/f but not accept it then that seems to violate Postel's admonition: Be *liberal* in what you *accept*, and *conservative* in what you send. Andrew: your workaround was to create a domain which would use 0/1 ... this idea will not behave like a boolean, because it does not accept 'true'/'false'. My replacement boolean type does accept all this input values and does emit 0/1 instead of 'f'/'t'. But since i have to create all the casts, operators and classes (like for boolean) again for my data type, i asked if someone know a more elegant way ... if not, i will use my data type. You can see the code here: http://andreas.scherbaum.la/writings/boolean.sql This are some hundred lines of SQL which are almost doubled from the boolean type. So maybe there's a way to avoid all this, but i haven't found one. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: Andrew: your workaround was to create a domain which would use 0/1 ... this idea will not behave like a boolean, because it does not accept 'true'/'false'. My replacement boolean type does accept all this input values and does emit 0/1 instead of 'f'/'t'. But since i have to create all the casts, operators and classes (like for boolean) again for my data type, i asked if someone know a more elegant way ... if not, i will use my data type. You can see the code here: http://andreas.scherbaum.la/writings/boolean.sql This are some hundred lines of SQL which are almost doubled from the boolean type. So maybe there's a way to avoid all this, but i haven't found one. Looks to me like this is the way to meet you requirements. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Binary data type with other output method
Hello, On Tue, 25 Dec 2007 20:11:45 -0500 Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I think you'd really need a separate type. But have you considered something simple like CREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1) i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: On Tue, 25 Dec 2007 20:11:45 -0500 Tom Lane wrote Andrew Dunstan [EMAIL PROTECTED] writes: I think you'd really need a separate type. But have you considered something simple like CREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1) i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem If you find a good solution, feel free to share. :-) I have had this annoy me for about 6 months now. I forget what configuration change I did, but booleans stopped working for exactly this reason (where they did work in the past). For lack of time or care, my PHP now has added === 't' ? true : false in various places... :-) Cheers, mark -- Mark Mielke [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Binary data type with other output method
On Wed, 26 Dec 2007 17:12:33 -0500 Mark Mielke wrote: Andreas 'ads' Scherbaum wrote: The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem If you find a good solution, feel free to share. :-) I have had this annoy me for about 6 months now. I forget what configuration change I did, but booleans stopped working for exactly this reason (where they did work in the past). For lack of time or care, my PHP now has added === 't' ? true : false in various places... :-) It has worked once? Never seen this working in PHP. I know, that some other database drivers for PHP do a better job here, but pure PHP fails. Yes, i can cast every result in every query or i can use your workaround, but this is silly and a common source of errors. Since this is a logic error, not a syntax error, you never find out in the first place but only with debugging. Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem. The answer is surely to fix the PHP driver rather than trying to mangle Postgres. The Perl DBD::Pg driver does not suffer this problem, so it can certainly be worked around (in fact in DBD::Pg you get a choice if 1/0 or t/f values for booleans). cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: Hello all, i'm in the need to create a boolean datatype which returns an integer instead of the usual 't'/'f'. Before anyone starts to point me at casts: it's a lot overhead to cast some hundred occurances beside the source of trouble, if you forget one. Do you really need a new datatype or just to change the output behaviour of the inbuilt type? That should be quite easy to do in just a few lines of code. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Binary data type with other output method
If you reduce your code you will have to leverage the bool data type. Why dont you provide a cast, I dont see an overhead in typbyval datatypes. On Dec 25, 2007 9:10 PM, Andrew Dunstan [EMAIL PROTECTED] wrote: Andreas 'ads' Scherbaum wrote: Hello all, i'm in the need to create a boolean datatype which returns an integer instead of the usual 't'/'f'. Before anyone starts to point me at casts: it's a lot overhead to cast some hundred occurances beside the source of trouble, if you forget one. Do you really need a new datatype or just to change the output behaviour of the inbuilt type? That should be quite easy to do in just a few lines of code. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster --Imad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Binary data type with other output method
Hello, On Tue, 25 Dec 2007 11:10:25 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: i'm in the need to create a boolean datatype which returns an integer instead of the usual 't'/'f'. Before anyone starts to point me at casts: it's a lot overhead to cast some hundred occurances beside the source of trouble, if you forget one. Do you really need a new datatype or just to change the output behaviour of the inbuilt type? That should be quite easy to do in just a few lines of code. Just another output behavior would be enough ... but without changing the PG source itself. Since the package comes with the distribution, there's no way to build PG from source. Kind regads -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Binary data type with other output method
Hello, On Tue, 25 Dec 2007 22:12:23 +0500 imad wrote: Why dont you provide a cast, I dont see an overhead in typbyval datatypes. I already have a cast from my type to PGs internal boolean ... or what do you mean? Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: Hello, On Tue, 25 Dec 2007 11:10:25 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: i'm in the need to create a boolean datatype which returns an integer instead of the usual 't'/'f'. Before anyone starts to point me at casts: it's a lot overhead to cast some hundred occurances beside the source of trouble, if you forget one. Do you really need a new datatype or just to change the output behaviour of the inbuilt type? That should be quite easy to do in just a few lines of code. Just another output behavior would be enough ... but without changing the PG source itself. Since the package comes with the distribution, there's no way to build PG from source. I think all you need to do it this: Take the code for boolout() in src/backend/utils/adt/bool.c, adjust it and and make it a new function boolout2. Use pgxs to make it a loadable module. Then load it and adjust the catalog entry for the bool type to use boolout2 instead of boolout. Caveat: I think you would need to redo the type adjustment after every restore, as it wouldn't be dumped. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Binary data type with other output method
Andrew Dunstan [EMAIL PROTECTED] writes: I think all you need to do it this: Take the code for boolout() in src/backend/utils/adt/bool.c, adjust it and and make it a new function boolout2. Use pgxs to make it a loadable module. Then load it and adjust the catalog entry for the bool type to use boolout2 instead of boolout. Then start fixing pg_dump, psql, and every other bit of client-side code that expects the boolean columns in the system catalogs to read out as 't'/'f' ... I think you'd really need a separate type. But have you considered something simple like CREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1) regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Binary data type with other output method
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I think all you need to do it this: Take the code for boolout() in src/backend/utils/adt/bool.c, adjust it and and make it a new function boolout2. Use pgxs to make it a loadable module. Then load it and adjust the catalog entry for the bool type to use boolout2 instead of boolout. Then start fixing pg_dump, psql, and every other bit of client-side code that expects the boolean columns in the system catalogs to read out as 't'/'f' ... hmm, good point. Oh, well. I think you'd really need a separate type. But have you considered something simple like CREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1) Of course, that's not exactly what he asked for. Maybe we need to know more about the case. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
