Re: BUG #13601: bit as quoted column in output

On Thu, Sep 3, 2015 at 12:21 AM, Vicky Soni - Quipment India
<vicky(dot)soni(at)quipment(dot)nl> wrote:
> Please refer following code and attached screenshots.
>
> create or replace function bit_return_testing()
> returns table (OutBit bit) as
> $BODY$
> declare SQL VARCHAR;
> Begin
> sql:='select cast(1 as bit) ';
> raise notice '%',SQL;
> RETURN QUERY EXECUTE SQL;
> end;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100
> ROWS 1000;
>
> select * from bit_return_testing();
>
> It give me output like this.
>
> [picture of PGAdmin showing a column's type as: "bit"]

In this case, Postgres is losing track of the typmod and giving your
client PQftype(...) = 1560 (the OID for bit) and PQfmod(...) = -1.
PGAdmin is then calling format_type(1560, -1) to find out how to
display that, which gives '"bit"' (bit with double quotes).

That's because format_type is designed to write out type names that
can survive round trips for use by pg_dump, and, somewhat suprisingly,
"bit" with double quotes means bit with typmod -1 whereas bit without
double quotes means bit with typmod 1 AKA bit(1). Gory details here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/format_type.c

> Now if I call same statement directly, i.e.
>
> select cast(1 as bit) ;
>
> It gives me output like this.
>
> [Picture of PGAdmin showing a column's type as: bit(1)]

In this case PQftype(...) = 1560 and PQfmod(...) = 1, which
format_type renders as 'bit(1)' (without double quotes).

> Now my question/doubt/concern was why did it throw “bit” to me into first example and not simple bit?

Functions don't track the typmods of arguments or return values
(including the out arguments that are used to implement RETURNS
TABLE). You can write them, but they're discarded, so your data type
bit AKA bit(1) was replaced with bit with typmod -1 AKA "bit".

--
Thomas Munro
http://www.enterprisedb.com