pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
6+ messages / 4 participants
[nested] [flat]
* BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
@ 2025-02-04 12:43 PG Bug reporting form <noreply@postgresql.org>
2025-02-04 15:13 ` 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>
2025-02-04 15:30 ` Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2025-02-04 12:43 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ugurlu2001@hotmail.com
The following bug has been logged on the website:
Bug reference: 18793
Logged by: Ugur YILMAZ
Email address: ugurlu2001@hotmail.com
PostgreSQL version: 17.2
Operating system: Windows 11 Pro
Description:
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.
You can see the dynamically created function example that I use below. In
addition to the sample function, I am also sharing the "Basic SQL" sentence
that returns "character varying (length indeterminate)" data. It also
returns the correct result.
I have been trying to solve this seemingly simple problem for a few days.
However, I have a special restriction on the Varchar(254) limit in my
development environment.
Correctly working SQL script:
-- kodx > returning character varying :: length indeterminate
SELECT idx,kodx, guidx
,round(has_toplam::numeric,2) as has
,round(gumus_toplam::numeric,2) as gumus
,round(usd_toplam::numeric,2) as usd
,round(eur_toplam::numeric,2) as eur
,round(trl_toplam::numeric,2) as trl
,round(gbp_toplam::numeric,2) as gbp
,round(chf_toplam::numeric,2) as chf
,round(platin_toplam::numeric,2) as platin
,round(alloy_toplam::numeric,2) as alloy
FROM crosstab('
SELECT idx, kodx, guidx, bt_kod, toplam_miktar FROM
(
SELECT cma.company_master_id as idx, bt.kod AS bt_kod, bt.sys_default,
SUM(cma.miktar) as toplam_miktar, cm.kod as kodx, cma.company_master_guid as
guidx FROM company_master_amounts AS cma
LEFT OUTER JOIN balance_types as bt ON cma.balance_types_guid = bt.guid
LEFT OUTER JOIN company_master as cm ON cm.id = cma.company_master_id
WHERE company_master_id = 3
GROUP BY cma.company_master_id, bt.kod, bt.sys_default, cm.kod,
cma.company_master_guid
) ORDER BY idx, sys_default DESC, bt_kod
'::TEXT,
' SELECT kod as kodx FROM balance_types ORDER BY sys_default DESC, kod, id
'::TEXT)
crosstab(idx integer, kodx character varying(200), guidx uuid
,has_toplam numeric(22,6),gumus_toplam numeric(22,6)
,usd_toplam numeric(22,6),eur_toplam numeric(22,6)
,trl_toplam numeric(22,6),gbp_toplam numeric(22,6)
,chf_toplam numeric(22,6),platin_toplam numeric(22,6)
,alloy_toplam numeric(22,6))
GROUP BY
has_toplam,gumus_toplam,usd_toplam,eur_toplam,trl_toplam,gbp_toplam,chf_toplam,platin_toplam,alloy_toplam,kodx,guidx,idx
ORDER BY kodx;
A function that returns a result of indefinite length in the form of
"character varying" instead of "Code ::varchar(200)". A "Returns Query"
function that creates a dynamic SQL script and returns a value in the "Table
Type" (second level - needed by the end user):
-- kodx ( field name kod on "return table" ) > returning character varying
(200)
/*
FUNCTION: public.get_balance_pivot_1c(integer)
DROP FUNCTION IF EXISTS public.get_balance_pivot_1c(integer);
Select * from get_balance_pivot_1c(3) ;
*/
CREATE OR REPLACE FUNCTION public.get_balance_pivot_1c(
pintcompany_id integer)
RETURNS TABLE(id integer, kod varchar(200), guid uuid, has numeric,
gumus numeric, usd numeric, eur numeric, trl numeric, gbp numeric, chf
numeric, platin numeric, alloy numeric)
LANGUAGE 'plpgsql'
COST 100
STABLE PARALLEL SAFE
ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
SELECT idx,kodx::varchar(200), guidx
,round(has_toplam::numeric,2) as has
,round(gumus_toplam::numeric,2) as gumus
,round(usd_toplam::numeric,2) as usd
,round(eur_toplam::numeric,2) as eur
,round(trl_toplam::numeric,2) as trl
,round(gbp_toplam::numeric,2) as gbp
,round(chf_toplam::numeric,2) as chf
,round(platin_toplam::numeric,2) as platin
,round(alloy_toplam::numeric,2) as alloy
FROM crosstab('
SELECT idx, kodx, guidx, bt_kod, toplam_miktar FROM
(
SELECT cma.company_master_id as idx, bt.kod AS bt_kod, bt.sys_default,
SUM(cma.miktar) as toplam_miktar, cm.kod as kodx, cma.company_master_guid as
guidx FROM company_master_amounts AS cma
LEFT OUTER JOIN balance_types as bt ON cma.balance_types_guid = bt.guid
LEFT OUTER JOIN company_master as cm ON cm.id = cma.company_master_id
WHERE company_master_id = 3
GROUP BY cma.company_master_id, bt.kod, bt.sys_default, cm.kod,
cma.company_master_guid
) ORDER BY idx, sys_default DESC, bt_kod
'::TEXT,
' SELECT kod as kodx FROM balance_types ORDER BY sys_default DESC, kod, id
'::TEXT) crosstab(idx integer, kodx character varying(200), guidx uuid,
has_toplam numeric(22,6),gumus_toplam numeric(22,6),usd_toplam
numeric(22,6),eur_toplam numeric(22,6),trl_toplam numeric(22,6),gbp_toplam
numeric(22,6),chf_toplam numeric(22,6),platin_toplam
numeric(22,6),alloy_toplam numeric(22,6))
GROUP BY
has_toplam,gumus_toplam,usd_toplam,eur_toplam,trl_toplam,gbp_toplam,chf_toplam,platin_toplam,alloy_toplam,kodx,guidx,idx
ORDER BY kodx;
/* WHERE company_master_id = ' || pintcompany_id || ' */
/* WHERE company_master_id = ' || '3' || ' */
END ;
$BODY$;
ALTER FUNCTION public.get_balance_pivot_1c(integer)
OWNER TO postgres;
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.
Best wishes...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
2025-02-04 12:43 BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query PG Bug reporting form <noreply@postgresql.org>
@ 2025-02-04 15:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-04 15:49 ` Re: 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>
2025-02-09 23:13 ` Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query David G. Johnston <david.g.johnston@gmail.com>
2025-02-10 00:55 ` Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 3 replies; 6+ messages in thread
From: Laurenz Albe @ 2025-02-04 15:13 UTC (permalink / raw)
To: ugurlu2001@hotmail.com; pgsql-bugs@lists.postgresql.org
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] 6+ messages in thread
* Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
2025-02-04 12:43 BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query PG Bug reporting form <noreply@postgresql.org>
2025-02-04 15:13 ` 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>
@ 2025-02-04 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2025-02-04 15:49 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: ugurlu2001@hotmail.com; pgsql-bugs@lists.postgresql.org
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Tue, 2025-02-04 at 12:43 +0000, PG Bug reporting form wrote:
>> 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.
> That is working asdesigned. Why is it a problem for you?
Indeed. See the Notes section in [1]:
The full SQL type syntax is allowed for declaring a function's
arguments and return value. However, parenthesized type modifiers
(e.g., the precision field for type numeric) are discarded by
CREATE FUNCTION. Thus for example CREATE FUNCTION foo
(varchar(10)) ... is exactly the same as CREATE FUNCTION foo
(varchar) ....
The RETURNS TABLE notation is just syntactic sugar for some output
arguments, it doesn't change this aspect.
Our general view of notations like varchar(10) is that the length
limit is a kind of column constraint and is to be enforced against
data "at rest" in a table. If you want something that's enforced on
the fly during expression evaluation, you'll need to use a domain
type with a CHECK constraint.
regards, tom lane
[1] https://www.postgresql.org/docs/current/sql-createfunction.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
2025-02-04 12:43 BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query PG Bug reporting form <noreply@postgresql.org>
2025-02-04 15:13 ` 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>
@ 2025-02-09 23:13 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2025-02-09 23:13 UTC (permalink / raw)
To: Ugur Yilmaz <ugurlu2001@hotmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sun, Feb 9, 2025 at 3:53 PM Ugur Yilmaz <ugurlu2001@hotmail.com> wrote:
> 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.
>
I am familiar with this interpretation of the meta-data. It is
indeed annoying that there is no satisfying solution to this. IMO the UI
should just detect the presence/absence of a newline in the data and render
the specific cell accordingly.
>
> * 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.
>
You would need to write such a script; but it doesn't seem necessary.
You've made your need clear and have a clear answer.
> * 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..
>
Correct.
> 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.
>
I'm not sure I understand why as well, but unless I was intent on producing
a patch to overcome the limitation the why of it is immaterial to me.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
2025-02-04 12:43 BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query PG Bug reporting form <noreply@postgresql.org>
2025-02-04 15:13 ` 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>
@ 2025-02-10 00:55 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2025-02-10 00:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ugur Yilmaz <ugurlu2001@hotmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sunday, February 9, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
Given that pg_dump already outputs the typmod-less code I’d have to suspect
that making specifying a typmod here an error would be better for our
reputation, and users, overall.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query
2025-02-04 12:43 BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query PG Bug reporting form <noreply@postgresql.org>
@ 2025-02-04 15:30 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2025-02-04 15:30 UTC (permalink / raw)
To: ugurlu2001@hotmail.com <ugurlu2001@hotmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tuesday, February 4, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:
. The
>
> exception is the varchar(n) fixed-length data type
>
That isn’t how this works. The (n) does not make it “fixed-width’. It
simply is a way to enforce a maximum length to the value but otherwise the
data type itself is still variable width. Mostly we recommend use of text,
dealing with constraints in other more global ways.
Correctly working SQL script:
I’ll wait for a “minimal reproducer” to dive into specifics if there are
still questions.
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.
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.
>
> I hope this situation, which I observed as a problem, will be fixed as soon
> as possible.
>
If the absence of the typmod as it is called (the (n)) is problem there
isn’t a change forthcoming to rework that part of the system. It’s a
limitation we are living with.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-02-10 00:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-04 12:43 BUG #18793: PLpgSQL Function Returning Type of Table is not match for varchar(n) data type via Return Query PG Bug reporting form <noreply@postgresql.org>
2025-02-04 15:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-04 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-09 23:13 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-10 00:55 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-04 15:30 ` David G. Johnston <david.g.johnston@gmail.com>
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