Re: Boolean output format

Thanks, Jeff.

The problem is that this would require that I rewrite many queries to
utilize the function... I'm currently using smallint to store the bools
because I want 0/1 as output. I'd like to move away from this but in php "f"
evaluates true!

I have followed a good deal of discussion regarding SET DATESTYLE and I
really was hoping that there was some way to SET BOOLEANSTYLE or something
like this.

Unfortunately, I find both the case statement and the sql function to be
overkill... My thinking is why should it require more work to deal with the
simplest of all data types... I very much still appreciate the response. :)

I think I am ultimately making a case for a new feature in a future version
of postgres. This is also important for people who want to port MySQL
applications to PostgreSQL! As a committed postgres advocate, I never want
to say we can't get there from here... (MySQL outputs bools as 1/0 if I am
not mistaken, though I haven't used it in some time!)

I regret I am not a real hacker or I'd work on this myself!

Best Regards,
Garo.

on 10/4/02 6:36 PM, Jeff Davis at list-pgsql-general(at)empires(dot)org wrote:

>
> The best way change the output style in general is a stored procedure.
> However, with booleans it's simple enough that you could write it out inline
> if you want:
> ------------------------------------------------------------------------------
> jdavis=> create table b(a bool);
> CREATE
> jdavis=> insert into b values('t');
> INSERT 67682 1
> jdavis=> insert into b values('f');
> INSERT 67683 1
> jdavis=> select case when a then 'YES' else 'NO' end from b ;
> case
> ------
> YES
> NO
> (2 rows)
> ------------------------------------------------------------------------------
> --
>
> The case statement basically just special cases the two values. In this case
> it of course changes 't' to 'YES' and 'f' to 'NO'.
>
> You could also make a SQL function out of it no problem:
>
> jdavis=> create function myfn(bool) returns text as 'select case when $1 then
> ''YES'' else ''NO'' end;' language 'sql';
>
> Then just use that in your selects:
> jdavis=> select myfn(a) from b;
> myfn
> ------
> YES
> NO
> (2 rows)
>
>
> Regards,
> Jeff Davis
>
>
> On Friday 04 October 2002 06:17 pm, Garo Hussenjian wrote:
>> A friend of mine has told me that using the Zope pgsql driver you can set
>> the output format of postgres booleans...
>>
>> Unfortunately, I'm using php and would like to do this also.
>>
>> Is the zope driver doing this or is it some sort of option that can be sent
>> when the connection is made or a query that can be run?
>>
>> Thanks,
>> Garo.
>>
>>
>> =-=-==-=-=-==
>>
>> Xapnet Internet Solutions
>> 1501 Powell St., Suite N
>> Emeryville, CA 94608
>>
>> Tel - (510) 655-9771
>> Fax - (510) 655-9775
>> Web - http://www.xapnet.com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

=-=-==-=-=-==

Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608

Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com