Обсуждение: BUG #17869: Inconsistency between PL/pgSQL Function Parameter Handling and SQL Query Results

The following bug has been logged on the website:

Bug reference:      17869
Logged by:          Jiangshan Liu
Email address:      jiangshan.liu@tju.edu.cn
PostgreSQL version: 15.2
Operating system:   Ubuntu 18.04
Description:

Dear PostgreSQL developers,
I would like to report a potential bug in the handling of fixed-length
character types in PL/pgSQL functions. As per the documentation, CHARACTER
without a length specifier are equivalent to CHARACTER(1) [1]. This is
confirmed when using them in SQL queries.

SELECT 'abc'::CHAR;
 bpchar
--------
 a
(1 row)

However, when passing fixed-length character types as parameters in PL/pgSQL
functions, the behavior seems to be different. The documentation states that
parenthesized type modifiers are discarded by CREATE FUNCTION, meaning that
CREATE FUNCTION foo (varchar(10)) is the same as CREATE FUNCTION foo
(varchar) [2].

CREATE OR REPLACE FUNCTION test(param CHAR) RETURNS TEXT AS $$
  BEGIN
    RAISE NOTICE '%', param;
    RETURN param;
  END; 
$$ LANGUAGE plpgsql;

SELECT * FROM test('abc');

 test
------
 abc
(1 row)

This indicates that the processing logic designed for CHARACTER without
length specifier does not work in PL/pgSQL parameter lists. Is this a bug in
the processing of PL/pgSQL parameter lists?

By the way, I'm currently working on how to correspond PL/pgSQL function
behavior to plain SQL, and I'm concerned about usages that should behave
consistently in PL/pgSQL functions and plain SQL, and this issue was
discovered in this work.

In the documentation it is mentioned that
Functions written in PL/pgSQL can accept as arguments any scalar or array
data type supported by the server [3].

This suggests that in terms of data types, there should be no difference
between PL/pgSQL functions and plain SQL for the same usage. But this design
in the parameter list seems to contradict this initial intention. Are there
any design concerns here?

Why not handle fixed-length CHARACTER as well as omitting length specifier
variable-length CHARACTER VARYING, in which case everything might make sense
[4].

[1]

https://www.postgresql.org/docs/15/datatype-character.html#:~:text=character%20without%20length%20specifier%20is%20equivalent%20to%20character(1)
[2]

https://www.postgresql.org/docs/15/sql-createfunction.html#:~:text=parenthesized%20type%20modifiers%20(e.g.%2C%20the%20precision%20field%20for%20type%20numeric)%20are%20discarded%20by%20CREATE%20FUNCTION
[3]

https://www.postgresql.org/docs/15/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS:~:text=Functions%20written%20in%20PL/pgSQL%20can%20accept%20as%20arguments%20any%20scalar%20or%20array%20data%20type%20supported%20by%20the%20server
[4]

https://www.postgresql.org/docs/15/datatype-character.html#:~:text=If%20character%20varying%20is%20used%20without%20length%20specifier%2C%20the%20type%20accepts%20strings%20of%20any%20size

The following bug has been logged on the website:

Bug reference:      17869
Logged by:          Jiangshan Liu
Email address:      jiangshan.liu@tju.edu.cn
PostgreSQL version: 15.2
Operating system:   Ubuntu 18.04
Description:       

However, when passing fixed-length character types as parameters in PL/pgSQL
functions, the behavior seems to be different. The documentation states that
parenthesized type modifiers are discarded by CREATE FUNCTION, meaning that
CREATE FUNCTION foo (varchar(10)) is the same as CREATE FUNCTION foo
(varchar) [2].

PG Bug reporting form <noreply@postgresql.org> writes:
> However, when passing fixed-length character types as parameters in PL/pgSQL
> functions, the behavior seems to be different. The documentation states that
> parenthesized type modifiers are discarded by CREATE FUNCTION, meaning that
> CREATE FUNCTION foo (varchar(10)) is the same as CREATE FUNCTION foo
> (varchar) [2].

I think you've misunderstood that.  Type modifiers are not applied
to function parameters.  Thus, this function declaration:

> CREATE OR REPLACE FUNCTION test(param CHAR) RETURNS TEXT AS $$

avers that the function takes any CHAR-type value regardless of length.
Had you written, say,

regression=# SELECT * FROM test('abc'::char);
NOTICE:  a
 test 
------
 a
(1 row)

the cast operation would enforce the "defaults to length 1" business;
but the function itself does not.

Generally speaking this is desirable because you wouldn't want to have
to write a different copy of test() for each string length you might
want to use it with.  If you are really intent on getting the other
behavior you could use a domain:

regression=# create domain c1 as char(1);
CREATE DOMAIN
regression=# CREATE OR REPLACE FUNCTION test(param c1) RETURNS TEXT AS $$
  BEGIN
    RAISE NOTICE '%', param;
    RETURN param;
  END; 
$$ LANGUAGE plpgsql;
CREATE FUNCTION
regression=# SELECT * FROM test('abc');
ERROR:  value too long for type character(1)
regression=# SELECT * FROM test('abc'::char);
NOTICE:  a
 test 
------
 a
(1 row)

This happens because there is no concept of a non-typmod-enforcing
cast to a domain.

            regards, tom lane