Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query

I am writing this answer as an addition to the answers I saw later from "Tom Lane" and "David G. Johnston" in this message tree.

First of all, I want to show the subject from a different perspective. The situation I am experiencing is a short summary of this perspective. In my development environment, the Varchar(n) type is normally displayed as an "Application Interface - Grid Display" up to the 254 character limit. If the Varchar(indeterminate length) case is the case, the data remains embedded in a "Memo pre-declaration" definition. In other words, the user cannot view the data directly.

For David's message:
* It simply is a way to enforce a maximum length to the value but otherwise the data type itself is still variable width : "What I exactly want is to limit the maximum length of the data type" I think I stated the expression incorrectly. In other words, I do not have a question mark about what varchar(n) means, but the result I expect is not a "text".

* I’ll wait for a “minimal reproducer” to dive into specifics if there are still questions. : Do I need to provide you with examples on this or a remote connection for my own development environment? I can collaborate as you need.

* Mostly functions drop the (n) specifier and deal with the data type itself. The function only wants a concrete value and doesn’t deal with enforcing constraints in its signature. You can add code to the body if you need that. :: This answer was a surprise to me. I don’t technically care about the function dropping the (n) specifier and identifier in the Database environment, but I do care about the “data type” returning “the type and properties” that I “want and define”.

* If the absence of the typmod as it is called (the (n)) is a problem there isn’t a change forthcoming to rework that part of the system. It’s a limitation we are living with. : As far as I can see, no changes or updates are planned for the current situation and the current situation will continue as it is..

For Tom's message:
I will test the use of "Check" and "Domain" but even if I get the result I want in general, it will take time to adapt it in the application layer. Or I will have to do extra (quite a lot) coding without trying it at all.

Thank you for the explanations and information. Although I find your answers convincing; I still can't understand why I can't get the desired result from the Varchar(n) type, whose -max- length I explicitly specified in the "Returns Table" statement.

With by best...

Ugur YILMAZ
________________________________
Gönderen: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Gönderildi: 4 Şubat 2025 Salı 18:13
Kime: ugurlu2001(at)hotmail(dot)com <ugurlu2001(at)hotmail(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Konu: Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query

On Tue, 2025-02-04 at 12:43 +0000, PG Bug reporting form wrote:
> I have a set of "Functions" that I created dynamically with PLPgSQL and are
> connected to each other in a chain.
>
> The first function dynamically creates the second function. The second
> function returns a set of values with a dynamic "Return Query" statement and
> a reference to "Returns Table (field1 type1, field2 type2 .... )".
>
> Basically, the result set seems to return exactly the desired result. The
> exception is the varchar(n) fixed-length data type. Although the result set
> is expected to be varchar(n) -a fixed-length value is expected-, a result of
> type character varying (length indeterminate) is obtained.
>
> [...]
>
> As far as I have determined, the Postgresql database engine somehow returns
> the varchar(n) - Fixed Length - data type as a "character varying" -
> indefinite length data type.
>
> I hope this situation, which I observed as a problem, will be fixed as soon
> as possible.

That is working asdesigned. Why is it a problem for you?

Yours,
Laurenz Albe