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

Hi Thomas,

In this case, we are not able to retrieve correct datatype of returning function when calling from Application. In our case it is DOTNET and we use NPGSQL.

Is this a bug? Can we expect fix for this or should we live with same?

Please advice.

Thanks & Regards,
Vicky Soni
Database Administrator

-----Original Message-----
From: Thomas Munro [mailto:thomas(dot)munro(at)enterprisedb(dot)com]
Sent: 23 September 2015 08:28
To: Vicky Soni - Quipment India <vicky(dot)soni(at)quipment(dot)nl>
Cc: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] 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