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(at)dunslane(dot)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(at)dunslane(dot)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 like
CREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1)

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)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(at)dunslane(dot)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(at)dunslane(dot)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(at)mielke(dot)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