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