Bug in CREATE FUNCTION with character type

Pavel Golub wrote:

Hello, Pgsql-bugs.

PostgreSQL version: 8.4.x
Operating system: All

If RETURNS TABLE clause of CREATE FUNCTION contain column of character type (withou
length specifier) it should be treated as character(1) according to
manual, but it look like text.
(http://www.postgresql.org/docs/8.4/static/datatype-character.html)

Yep. "character without length specifier is equivalent to character(1)"

To sum up the below, yes, I can reproduce the issue you describe and yes
I think it's a bug.

CREATE OR REPLACE FUNCTION test_char_function()
RETURNS TABLE(id int, salesourcecode character) AS
$BODY$
VALUES (1, 'one'), (2, 'two'), (3, 'three');
$BODY$
LANGUAGE 'sql'

\df reports:

Schema | public
Name | test_char_function
Result data type | TABLE(id integer, salesourcecode character)
Argument data types |
Type | normal

and the output is:

regress=> select test_char_function();
test_char_function
--------------------
(1,one)
(2,two)
(3,three)
(3 rows)

... which is completely bogus given the truncation rules for character
types and the rule quoted from the documentation above.

CREATE TABLE behaves correctly.

CREATE TYPE behaves like a RETURNS TABLE function, interpreting
"character" as unbounded and text-like. If you rewrite your function to
read:

CREATE TYPE testtype AS (id int, salesourcecode character);
CREATE OR REPLACE FUNCTION test_char_function()
RETURNS SETOF testtype AS
$BODY$
VALUES (1, 'one'), (2, 'two'), (3, 'three');
$BODY$
LANGUAGE 'sql';

... you see the same behaviour, despite:

regress=> \d testtype
Composite type "public.testtype"
Column | Type
----------------+--------------
id | integer
salesourcecode | character(1)

If I select the output of the function into another table:

regress=> select * into testtab from test_char_function();
regress=> \d testtab
Table "public.testtab"
Column | Type | Modifiers
----------------+---------+-----------
id | integer |
salesourcecode | bpchar |
regress=>select * from testtab;
id | salesourcecode
----+----------------
1 | one
2 | two
3 | three
(3 rows)

the type appears to have become "bpchar".

If I explicitly create "testtab" first using the same definition as the
function uses, copied 'n' pasted from "Result data type, then try to
insert the result from the function into it, the attempt fails if the
function was defined RETURNS TABLE:

regress=> create TABLE testtab(id integer, salesourcecode character);
regress=>insert into testtab select * from test_char_function();
ERROR: value too long for type character(1)

and *SUCCEEDS* if it was defined "returns setof testtype", resulting in
data in the table that VIOLATES THE LENGTH CONSTRAINT FOR THAT TABLE:

regress=> create TABLE testtab(id integer, salesourcecode character);
CREATE TABLE
regress=> insert into testtab select * from test_char_function();
INSERT 0 3
regress=> \d testtab
Table "public.testtab"
Column | Type | Modifiers
----------------+--------------+-----------
id | integer |
salesourcecode | character(1) |

regress=> select * from testtab;
id | salesourcecode
----+----------------
1 | one
2 | two
3 | three
(3 rows)

... so Pg is definitely applying a different rule to the interpretation
of unqualified "character" in RETURNS TABLE functions to what it applies
to CREATE TABLE, and is getting pretty darn confused between its
character types in general.

I'd certainly call this a bug, if not a couple of different bugs. Er,
help?!?

--
Craig Ringer

I'd certainly call this a bug, if not a couple of different bugs.

There seems to be more. I see a problem not just with unqualified char not being treated equivalent to char(1). I also see a general problem with RETURNS TABLE disregarding any explicit lengths declared for any character type. For example, if you declare a RETURNS TABLE column as VARCHAR(15), it seems to actually consider it as if you had declared it unqualified VARCHAR, equivalent to TEXT. Similarly, a column defined as CHAR(2) will also be treated as unqualified CHAR without an explicit length, with the further problem reported by Pavel of being treated as unlimited length instead of equivalent to CHAR(1) as expected. In other words, it seems to be effectively impossible to declare explicit lengths for RETURNS TABLE character type columns.

This doesn't happen with the RETURNS SETOF variation, only RETURNS TABLE.

Regards,

--- Kevin

2010/4/14 Kevin J Bluck <kevin.bluck@netce.com>:

I'd certainly call this a bug, if not a couple of different bugs.

There seems to be more. I see a problem not just with unqualified char not being treated equivalent to char(1). I also see a general problem with RETURNS TABLE disregarding any explicit lengths declared for any character type. For example, if you declare a RETURNS TABLE column as VARCHAR(15), it seems to actually consider it as if you had declared it unqualified VARCHAR, equivalent to TEXT. Similarly, a column defined as CHAR(2) will also be treated as unqualified CHAR without an explicit length, with the further problem reported by Pavel of being treated as unlimited length instead of equivalent to CHAR(1) as expected. In other words, it seems to be effectively impossible to declare explicit lengths for RETURNS TABLE character type columns.

This doesn't happen with the RETURNS SETOF variation, only RETURNS TABLE.

Regards,

RETURNS TABLE (x int, y int) is equal to CREATE FUNCTION foo(.. OUT x
int, OUT y int) RETURNS SETOF RECORD. But PostgreSQL functions ignore
typmod for parameters - so it is not bug, it is feature :(

Pavel Stehule

Pavel Golub <pavel@microolap.com> writes:

Example:

CREATE OR REPLACE FUNCTION test_char_function()
RETURNS TABLE(id int, salesourcecode character) AS
$BODY$
VALUES (1, 'one'), (2, 'two'), (3, 'three');
$BODY$
LANGUAGE 'sql'

pqfmod function from libpq library for salesourcecode column returns
0, which is wrong. Who can ever imagine bpchar of length 0? :)

I get -1 (indicating unspecified), which is what I'd expect. You
sure about the zero?

regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:

RETURNS TABLE (x int, y int) is equal to CREATE FUNCTION foo(.. OUT x
int, OUT y int) RETURNS SETOF RECORD. But PostgreSQL functions ignore
typmod for parameters - so it is not bug, it is feature :(

Yeah. Or at least, this is not something that can be changed without a
great deal of work --- and application breakage, more than likely.

You could possibly get the length to be enforced by storing into a local
variable of the record type before returning it. I think plpgsql does
account for typmod when assigning to locals.

regards, tom lane

PostgreSQL functions ignore typmod for parameters...

...not something that can be changed without a great
deal of work --- and application breakage...

Thanks to all for the clarifications. I can make it return what I wanted with qualified character types by returning a SETOF RECORD types, where the record type is a table or aggregate type. The key, it seems, is to avoid using output parameters. But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be legal to specify them in that clause? I don't know. It just *seems* like a bug if you don’t know why your carefully crafted return type is being partially ignored. But now I do, so thanks again.

I do think Pavel G. has a real bug with the char thing, though.

--- Kevin

"Kevin J Bluck" <kevin.bluck@netce.com> writes:

But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be
legal to specify them in that clause?

Yeah, possibly. CREATE FUNCTION has historically accepted (and then
discarded) typmod information for all function parameter and result
types; RETURNS TABLE doesn't seem particularly different from other
cases here. We could tighten that up, but again it's not clear whether
the probable ensuing application breakage would be worth the reduction
in astonishment quotient.

I do think Pavel G. has a real bug with the char thing, though.

No, it's exactly the same thing: we're accepting and then throwing away
the typmod. The fact that it's implicit rather than written out doesn't
change that.

char would be a particularly nasty case if we did reject typmod
specifications for function arguments/results, because there is no
standard syntax for specifying char without a defined max length.
You'd have to fall back to writing "bpchar", which isn't going to
make people happy either...

regards, tom lane

On 15/04/2010 8:05 AM, Tom Lane wrote:

"Kevin J Bluck"<kevin.bluck@netce.com> writes:

But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be
legal to specify them in that clause?

Yeah, possibly. CREATE FUNCTION has historically accepted (and then
discarded) typmod information for all function parameter and result
types; RETURNS TABLE doesn't seem particularly different from other
cases here. We could tighten that up, but again it's not clear whether
the probable ensuing application breakage would be worth the reduction
in astonishment quotient.

I do think Pavel G. has a real bug with the char thing, though.

No, it's exactly the same thing: we're accepting and then throwing away
the typmod. The fact that it's implicit rather than written out doesn't
change that.

If the function `RETURNS SETOF user_composite_type' and
user_composite_type has type length-specifiers, that data appears to be
disregarded there too, so the function can return values of
user_composite_type that violate the constraints of that type. See my
original follow-up on the bug.

That was pretty high on my personal atonishment meter. Using `SELECT ...
INTO' with the function I was able to create a table with data in it
that violated type constraints. IMO that's a bug. If functions
disregarding typmod information is the window that lets that bogus data
into the table, IMO that's a problem.

So - I do think there's a bug here, if _tables_ can be created with
character(1) fields containing three- or four-character strings.

--
Craig Ringer

Craig Ringer <craig@postnewspapers.com.au> writes:

So - I do think there's a bug here, if _tables_ can be created with
character(1) fields containing three- or four-character strings.

[ shrug... ] If you can demonstrate such a behavior, I'd agree it's a
bug, but the examples at hand have nothing to do with what gets stored
into tables.

regards, tom lane

On 15/04/2010 10:25 AM, Tom Lane wrote:

Craig Ringer<craig@postnewspapers.com.au> writes:

So - I do think there's a bug here, if _tables_ can be created with
character(1) fields containing three- or four-character strings.

[ shrug... ] If you can demonstrate such a behavior, I'd agree it's a
bug, but the examples at hand have nothing to do with what gets stored
into tables.

regress=> CREATE TYPE testtype AS (id int, salesourcecode character);
CREATE TYPE
regress=> CREATE OR REPLACE FUNCTION test_char_function()
RETURNS SETOF testtype AS
$BODY$
VALUES (1, 'one'), (2, 'two'), (3, 'three');
$BODY$
LANGUAGE 'sql';
CREATE FUNCTION
regress=> SELECT * INTO test_tab FROM test_char_function();
SELECT
regress=> select * from test_tab;
id | salesourcecode
----+----------------
1 | one
2 | two
3 | three
(3 rows)

craig=> \d test_tab
Table "public.test_tab"
Column | Type | Modifiers
----------------+--------------+-----------
id | integer |
salesourcecode | character(1) |

--
Craig Ringer

2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:

"Kevin J Bluck" <kevin.bluck@netce.com> writes:

But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be
legal to specify them in that clause?

Yeah, possibly.  CREATE FUNCTION has historically accepted (and then
discarded) typmod information for all function parameter and result
types; RETURNS TABLE doesn't seem particularly different from other
cases here.  We could tighten that up, but again it's not clear whether
the probable ensuing application breakage would be worth the reduction
in astonishment quotient.

I think, so RETURNS TABLE can be modified for returning typmode
without significant problems - this function is called in table
context and I don't see any problematic use case.

Pavel

Hello, Tom.

You wrote:

TL> Pavel Golub <pavel@microolap.com> writes:

Example:

CREATE OR REPLACE FUNCTION test_char_function()
RETURNS TABLE(id int, salesourcecode character) AS
$BODY$
VALUES (1, 'one'), (2, 'two'), (3, 'three');
$BODY$
LANGUAGE 'sql'

pqfmod function from libpq library for salesourcecode column returns
0, which is wrong. Who can ever imagine bpchar of length 0? :)

TL> I get -1 (indicating unspecified), which is what I'd expect. You
TL> sure about the zero?

TL> regards, tom lane

My fault. Yes, of course, it's -1

--
With best wishes,
Pavel mailto:pavel@gf.microolap.com

Pavel Stehule <pavel.stehule@gmail.com> writes:

I think, so RETURNS TABLE can be modified for returning typmode
without significant problems - this function is called in table
context and I don't see any problematic use case.

RETURNS TABLE is just a shorthand for some OUT parameters. I don't
believe it's either easy or a good idea to make it work differently
from every other function-argument-or-result case.

regards, tom lane

2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I think, so RETURNS TABLE can be modified for returning typmode
without significant problems - this function is called in table
context and I don't see any problematic use case.

RETURNS TABLE is just a shorthand for some OUT parameters.  I don't
believe it's either easy or a good idea to make it work differently
from every other function-argument-or-result case.

I don't know now. It minimally have to be documented

foodmart=# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 121,506 ms
foodmart=# select * from f();
a | b
-------+---------
Pavel | Stehule
(1 row)

Time: 0,718 ms
foodmart=# create table x as select * from f();
SELECT
Time: 105,357 ms
foodmart=# \d x
Table "public.x"
Column | Type | Modifiers
--------+-------------------+-----------
a | character varying |
b | character varying |

workaround is relative simple

foodmart=# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 1,009 ms
foodmart=# create table x as select a::varchar(20), b::varchar(20)
from (select * from f()) x ;
SELECT
Time: 48,592 ms
foodmart=# \d x
Table "public.x"
Column | Type | Modifiers
--------+-----------------------+-----------
a | character varying(20) |
b | character varying(20) |

Regards
Pavel Stehule

Pavel Stehule wrote:

2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I think, so RETURNS TABLE can be modified for returning typmode
without significant problems - this function is called in table
context and I don't see any problematic use case.

RETURNS TABLE is just a shorthand for some OUT parameters. ?I don't
believe it's either easy or a good idea to make it work differently
from every other function-argument-or-result case.

I don't know now. It minimally have to be documented

Can you suggest some documentation?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

2010/5/31 Bruce Momjian <bruce@momjian.us>:

Pavel Stehule wrote:

2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I think, so RETURNS TABLE can be modified for returning typmode
without significant problems - this function is called in table
context and I don't see any problematic use case.

RETURNS TABLE is just a shorthand for some OUT parameters. ?I don't
believe it's either easy or a good idea to make it work differently
from every other function-argument-or-result case.

I don't know now. It minimally have to be documented

Can you suggest some documentation?

some like "typmod in declared parameters are ignored - so returned
table can not be same as table declared by CREATE STATEMENT."

Pavel