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

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