Обсуждение: Where is the char and varchar length in pg_catalog for function input variables

I have searched and searched and just cannot find the maximum lengths for
input variables in a function

i.e.

CREATE FUNCTION test(input1 char(5), input2 varchar(50))
RETURNS void AS
$$RAISE NOTICE('%,%'), $1, $2;$$
LANGUAGE plpgsql;


Where do I find the 5 and the 50 it has to be somewhere I have searched
through
pg_proc
pg_type
pg_attribute (whose attlen only relates to tables)
pg_type

and all possible manner of joining these tables.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

jam3 <jamorton3@gmail.com> writes:
> I have searched and searched and just cannot find the maximum lengths for
> input variables in a function

> CREATE FUNCTION test(input1 char(5), input2 varchar(50))
> RETURNS void AS
> $$RAISE NOTICE('%,%'), $1, $2;$$
> LANGUAGE plpgsql;

> Where do I find the 5 and the 50 it has to be somewhere

No, it doesn't have to be, and it isn't.  As far as PG is concerned,
the inputs to this function are just of type "char" and "varchar".
You're allowed to write extra decoration but it's ignored.

            regards, tom lane

2012/9/5 Pavan Deolasee <pavan.deolasee@gmail.com>:
>
>
> On Wed, Sep 5, 2012 at 9:10 PM, jam3 <jamorton3@gmail.com> wrote:
>>
>> I have searched and searched and just cannot find the maximum lengths for
>> input variables in a function
>>
>> i.e.
>>
>> CREATE FUNCTION test(input1 char(5), input2 varchar(50))
>> RETURNS void AS
>> $$RAISE NOTICE('%,%'), $1, $2;$$
>> LANGUAGE plpgsql;
>>
>>
>> Where do I find the 5 and the 50 it has to be somewhere I have searched
>> through
>> pg_proc
>> pg_type
>> pg_attribute (whose attlen only relates to tables)
>> pg_type
>>
>> and all possible manner of joining these tables.
>>
>
> Hmm. I only looked at the code and hence don't have a definite answer. But
> it seems that information is not stored anywhere. That might explain why the
> function you mentioned accepts parameters with any character length.
>

yes, this information is just ignored - functions drops typmods

Regards

Pavel

> Thanks,
> Pavan
>
>

Yeah thats what I was starting to wonder if those lengths basically mean
nothing. I am writing a ton of functions to unit test all of the functions
in our app and am generating random strings and would like to pass the
lengths to my random string generator so if it's varchar 50 I am generating
a string between 0 and 50 length but since I can't find the length value I
guess I am just going to put an arbitrary length in.

Would be nice to know what exactly is going on when you have a length
specified on an input variable in pg_catalog.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722850.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Duh never mind I call brain cloud on that one, and thanks for all the help.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722880.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

How does postgres figure this out to throw the error msg?

create table test_table
(
    column1 char(10),
    column2 varchar(20)
) without oids;


create or replace function test1(c1 char(10), c2 varchar(20))
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql

select test1('1234567890','ABCDEFGHIJKLMNOPQRST')

select * from test_table;
-- 1234567890, ABCDEFGHIJKLMNOPQRST

select test1('this is way way longer than 10 characters','this is way way
way way way way way way way way way way longer than 20 characters')

ERROR:  value too long for type character(10)
CONTEXT:  SQL statement "insert into test_table values ($1, $2)"
PL/pgSQL function "test1" line 3 at SQL statement

********** Error **********

ERROR: value too long for type character(10)



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722876.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

This is what I meant to post

drop table test_table;
create table test_table
(
    column1 char(20),
    column2 varchar(40)
) without oids;


drop function test1(char(10), varchar(20));
create or replace function test1(c1 char(10), c2 varchar(20))
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql

select
test1('12345678900123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD')

select * from test_table;
12345678900123456789, ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD


Just showing that it does indeed not use the length in at all, and this just
seems wrong. I can definetly see situations where someone would put a length
on a in put var and get an an unexpected result, like the one above.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722881.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

jam3 <jamorton3@gmail.com> wrote:

> create or replace function test1(c1 char(10), c2 varchar(20))

> Just showing that it does indeed not use the length in at all

Correct.  That is functioning as intended and is not likely to
change any time soon.

You might consider using domains:

drop function if exists test1(c1 t1, c2 t2);
drop table if exists test_table;
drop domain if exists t1;
drop domain if exists t2;

create domain t1 varchar(10);
create domain t2 varchar(20);
create table test_table
(
  column1 char(20),
  column2 varchar(40)
) without oids;
create or replace function test1(c1 t1, c2 t2)
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql;
select
test1('12345678900123456789',
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD');
select * from test_table;

-Kevin

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of jam3
> Sent: Wednesday, September 05, 2012 3:34 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Re: Where is the char and varchar length in pg_catalog
> for function input variables
>
> This is what I meant to post
>
> ..................
>
> Just showing that it does indeed not use the length in at all, and this
just
> seems wrong. I can definetly see situations where someone would put a
> length on a in put var and get an an unexpected result, like the one
above.
>

You can argue it is wrong, and I'd tend to agree.  But that is how things
are until someone decides it is painful enough to implement a better way.

It is a documented situation though suggestions for improvements there are
always welcome.

If/when you care you can implement adhoc validation inside the function.

Discoverability via meta-data is the nice but lacking ability with the
current model but for arbitrary length and precision/scale specifications
that ability has limited (but non-zero) value. For better and worse you can
"extend" the system tables and include the meta-data that you feel is
necessary to make the system work.  It is a much less invasive procedure
than altering the catalogs themselves.

David J.

>
> How does postgres figure this out to throw the error msg?
>
>
> select test1('this is way way longer than 10 characters','this is way way
way
> way way way way way way way way way longer than 20 characters')
>
> ERROR:  value too long for type character(10)
> CONTEXT:  SQL statement "insert into test_table values ($1, $2)"
> PL/pgSQL function "test1" line 3 at SQL statement
>
> ********** Error **********
>
> ERROR: value too long for type character(10)
>

When it goes to execute:

INSERT INTO test_table ('this is way way ...', 'this is way way way...')

The char(10) type definition for test_table.column1 is too short to hold the
supplied value (stored in $1 in the function) and throws an error.

The length of $1 and $2 inside the function are however long the input
values are because they ignore the length specifier on the function call
types.

If you want to guarantee that the INSERT will work you would need to write:

INSERT INTO test_table VALUES ( $1::char(10), $2::varchar(20) )

This tells PostgreSQL to truncate the supplied value at whatever specified
length is noted; the same as writing substring($1, 1, 10)::char or
substring($1, 1, 20)::varchar though whether "char" and "varchar" differ in
their behavior in this respect I do not know.  It is generally not
recommended to use "char"

David J.

"David Johnston" <polobo@yahoo.com> wrote:

> If you want to guarantee that the INSERT will work you would need
> to write:
>
> INSERT INTO test_table VALUES ( $1::char(10), $2::varchar(20) )

Note that this will quietly cut off the tail end of the supplied
data, so it should only be used when that is desirable.  It is
generally better to throw an error than to lose data.

> whether "char" and "varchar" differ in their behavior in this
> respect I do not know.

Per the SQL standard, they both throw an error on attempts to assign
an oversized value, but allow truncation through explicit casts.

> It is generally not recommended to use "char"

Right.  It is supported because the standard specifies it and its
behavior, but the semantics of char(n) are weird and the
performance, in PostgreSQL, is generally worse for char(n) than
varchar(n) or text.

-Kevin