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

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...

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

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

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
Ican'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

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.