BUG #17869: Inconsistency between PL/pgSQL Function Parameter Handling and SQL Query Results

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17869: Inconsistency between PL/pgSQL Function Parameter Handling and SQL Query Results
Дата
Msg-id 17869-f049fe97d6a4491b@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17869: Inconsistency between PL/pgSQL Function Parameter Handling and SQL Query Results  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: BUG #17869: Inconsistency between PL/pgSQL Function Parameter Handling and SQL Query Results  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Alexander Lakhin
Дата:
Сообщение: Re: BUG #17858: ExecEvalArrayExpr() leaves uninitialised memory for multidim array with nulls
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: BUG #17869: Inconsistency between PL/pgSQL Function Parameter Handling and SQL Query Results