Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query - Mailing list pgsql-bugs
| From | Ugur Yilmaz |
|---|---|
| Subject | Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query |
| Date | |
| Msg-id | DU0PR10MB7142A855C86EC71EAD9C9F1CC0F32@DU0PR10MB7142.EURPRD10.PROD.OUTLOOK.COM Whole thread Raw |
| In response to | Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query (Laurenz Albe <laurenz.albe@cybertec.at>) |
| Responses |
Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
Re: Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query |
| List | pgsql-bugs |
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.albe@cybertec.at>
Gönderildi: 4 Şubat 2025 Salı 18:13
Kime: ugurlu2001@hotmail.com <ugurlu2001@hotmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Konu: Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
Gönderildi: 4 Şubat 2025 Salı 18:13
Kime: ugurlu2001@hotmail.com <ugurlu2001@hotmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.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
> 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
pgsql-bugs by date:
Previous
From: Ugur YilmazDate:
Subject: Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
Next
From: "David G. Johnston"Date:
Subject: Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query