Thread: Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)
> 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 RETU= RNS TABLE disregarding any explicit lengths declared for any character type= . For example, if you declare a RETURNS TABLE column as VARCHAR(15), it see= ms to actually consider it as if you had declared it unqualified VARCHAR, e= quivalent to TEXT. Similarly, a column defined as CHAR(2) will also be trea= ted as unqualified CHAR without an explicit length, with the further proble= m reported by Pavel of being treated as unlimited length instead of equival= ent to CHAR(1) as expected. In other words, it seems to be effectively impo= ssible 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 no= t being treated equivalent to char(1). I also see a general problem with RE= TURNS TABLE disregarding any explicit lengths declared for any character ty= pe. For example, if you declare a RETURNS TABLE column as VARCHAR(15), it s= eems 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 tr= eated as unqualified CHAR without an explicit length, with the further prob= lem reported by Pavel of being treated as unlimited length instead of equiv= alent to CHAR(1) as expected. In other words, it seems to be effectively im= possible to declare explicit lengths for RETURNS TABLE character type colum= ns. > > 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 > > --- Kevin > > > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
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 wi= th qualified character types by returning a SETOF RECORD types, where the r= ecord type is a table or aggregate type. The key, it seems, is to avoid usi= ng 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 jus= t *seems* like a bug if you don=E2=80=99t know why your carefully crafted r= eturn 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. =C2=A0CREATE 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. =C2=A0We could tighten that up, but again it's not clear whet= her > 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 > >> 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. =C2=A0The fact that it's implicit rather than written out doe= sn'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... > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, tom lane > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
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. =C2=A0I 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=3D# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 121,506 ms
foodmart=3D# select * from f();
a | b
-------+---------
Pavel | Stehule
(1 row)
Time: 0,718 ms
foodmart=3D# create table x as select * from f();
SELECT
Time: 105,357 ms
foodmart=3D# \d x
Table "public.x"
Column | Type | Modifiers
--------+-------------------+-----------
a | character varying |
b | character varying |
workaround is relative simple
foodmart=3D# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 1,009 ms
foodmart=3D# create table x as select a::varchar(20), b::varchar(20)
from (select * from f()) x ;
SELECT
Time: 48,592 ms
foodmart=3D# \d x
Table "public.x"
Column | Type | Modifiers
--------+-----------------------+-----------
a | character varying(20) |
b | character varying(20) |
Regards
Pavel Stehule
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>
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 > > -- > =C2=A0Bruce Momjian =C2=A0<bruce@momjian.us> =C2=A0 =C2=A0 =C2=A0 =C2=A0h= ttp://momjian.us > =C2=A0EnterpriseDB =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 http://enterprisedb.com > > =C2=A0+ None of us is going to be here forever. + > >