Thread: Binary data type with other output method
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. And so i asked myself, if there is an easier way than my current approach. Right now i'm creating input/output functions, the datatype and a lot of casts and operators (350 lines SQL) just to get another output: CREATE FUNCTION boolean2_in(cstring) RETURNS boolean2 AS 'boolin' LANGUAGE internal STRICT; CREATE FUNCTION boolean2_out(boolean2) RETURNS cstring AS 'int2out' LANGUAGE internal STRICT; CREATE FUNCTION boolean2_recv(internal) RETURNS boolean2 AS 'boolrecv' LANGUAGE internal STRICT; CREATE FUNCTION boolean2_send(boolean2) RETURNS bytea AS 'boolsend' LANGUAGE internal STRICT; CREATE TYPE boolean2 ( input = boolean2_in, output = boolean2_out, receive = boolean2_recv, send = boolean2_send, internallength= 1, alignment = char, storage = plain, passedbyvalue ); CREATE CAST (boolean2 AS boolean) WITHOUT FUNCTION AS ASSIGNMENT; CREATE CAST (boolean AS boolean2) WITHOUT FUNCTION AS ASSIGNMENT; ... and so on. Can i have this in an easier way? Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group
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
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 <andrew@dunslane.net> 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
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
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
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
Andrew Dunstan <andrew@dunslane.net> 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 likeCREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1)
regards, tom lane
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> 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.
Hello, On Tue, 25 Dec 2007 20:11:45 -0500 Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> 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
Andreas 'ads' Scherbaum wrote: > On Tue, 25 Dec 2007 20:11:45 -0500 Tom Lane wrote >> Andrew Dunstan <andrew@dunslane.net> 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 <mark@mielke.cc>
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)
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
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
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
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
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
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
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