Re: Is there a way to return "true"/"false" string for boolean type?

On Tue, Jan 7, 2014 at 2:55 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> On Tue, Jan 7, 2014 at 10:41 AM, ChoonSoo Park <luispark(at)gmail(dot)com> wrote:
>
>> On Tue, Jan 7, 2014 at 1:29 PM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:
>>
>>> On 7 January 2014 19:11, ChoonSoo Park <luispark(at)gmail(dot)com> wrote:
>>>
>>>> Hello Gurus,
>>>>
>>>> I have several tables with lots of boolean columns.
>>>> When I run select query for the tables, I always get 't' or 'f' for
>>>> boolean types.
>>>>
>>>> Is there a way to return 'true' or 'false' string for boolean type
>>>> except using CASE WHEN ... clause?
>>>> I mean global postgres configuration setting to return 'true'/'false'
>>>> instead of t/f.
>>>>
>>>> Thank you,
>>>> Choon Park
>>>>
>>>
>>> Hi,
>>> if you cast the boolean values to text, then you should get
>>> 'true'/'false':
>>>
>>> SELECT true::boolean::text, false::boolean::text;
>>> text | text
>>> ------+-------
>>> true | false
>>> (1 row)
>>>
>>> Does it solve your problem? Why do you want to have true/false instead
>>> of t/f?
>>>
>>> regards,
>>> Szymon
>>>
>>
>> In the custom stored function, I'm returning a resultset using hstore
>> function.
>> RETURN QUERY SELECT a few other columns, hstore(t.*) FROM table t WHERE
>> condition.
>>
>> I don't want to change it to
>>
>> SELECT a few other columns, hstore('c1', CAST(t.c1 AS TEXT)) ||
>> hstore('c2', CAST(t.c2 AS TEXT)) || ...hstore('cn', t.cn::text) || ...
>> FROM table t WHERE condition.
>>
>
> Can you use json instead of hstore?
>
> # select * from test;
> id | b
> ----+---
> 1 | t
> 2 | f
>
> # select to_json(test) from test;
> to_json
> --------------------
> {"id":1,"b":true}
> {"id":2,"b":false}
>
> Joe
>
>
to_json can be a good solution for me. Unfortunately, at this moment, we
don't have a plan to upgrade 9.1 to 9.3. I should still rely on hstore
function to hold key/value pairs.

-Choon Park