pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedYnt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
3+ messages / 2 participants
[nested] [flat]
* Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
@ 2025-02-09 21:22 ` Ugur Yilmaz <ugurlu2001@hotmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ugur Yilmaz @ 2025-02-09 21:22 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Sorry for late reply, I've been out of town for a while and have only just been able to answer my emails.
As I explain, the result set "looks like" working as designed but not like so.
The result set I expect is a fixed length value: (varchar 200)
However, the result set I get is a varchar (indefinite length) value.
In my application development environment, it is important for the length information to be fixed as much as the type of the data type...
In summary: " varchar (indefine) != varchar (200)"
________________________________
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
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
@ 2025-02-09 22:34 ` Ugur Yilmaz <ugurlu2001@hotmail.com>
2025-02-09 23:19 ` Re: Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 3+ messages in thread
From: Ugur Yilmaz @ 2025-02-09 22:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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".
* Ill 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 doesnt 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 dont 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 isnt a change forthcoming to rework that part of the system. Its 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
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
2025-02-09 22:34 ` Ynt: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query Ugur Yilmaz <ugurlu2001@hotmail.com>
@ 2025-02-09 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-02-09 23:19 UTC (permalink / raw)
To: Ugur Yilmaz <ugurlu2001@hotmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Ugur Yilmaz <ugurlu2001@hotmail.com> writes:
> 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.
Yeah. There is about zero chance that we are going to rework that:
the decision that function argument and result types are identified
by type OID alone is ancient and deeply rooted. Even ignoring the
costs of changing a lot of code, there are semantic problems.
For example, should we allow both of these functions to exist
concurrently?
create function f(varchar(100)) returns ...;
create function f(varchar(200)) returns ...;
If so, which do we pick when we're uncertain about the length
of the argument value?
You could however make a reasonable case that we should not accept
"varchar(200)" in contexts where we're going to ignore the "(200)"
part. That's pretty ancient too, cf this comment in gram.y:
* We can catch over-specified arguments here if we want to,
* but for now better to silently swallow typmod, etc.
* - thomas 2000-03-22
I'm not sure whether rejecting such things would make more people
happy than it made unhappy.
In any case, there's a whole lot of history here and a lot of
reason to worry about backwards compatibility.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-02-09 23:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-09 21:22 ` Ugur Yilmaz <ugurlu2001@hotmail.com>
2025-02-09 22:34 ` Ugur Yilmaz <ugurlu2001@hotmail.com>
2025-02-09 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox