Record returning function accept not matched columns declaration

I had this issue as well.

There is much inet talk about a bug in PGadmin in creating functions v cli, and how pgAdmin doesn't like some functions.

I had to change my function to this method to get out of the error.

The error wants you to have the columns in the function, not at SELECT statement.

at least this was my issue, your mileage may vary.

DROP FUNCTION IF EXISTS schema.function_name;

CREATE OR REPLACE FUNCTION schema.function_name;(
_typeahead character varying,
_rolename character varying,
_requiremisconductaccess character varying,
_test1 character varying,
_test2 character varying,
_test3 character varying)
RETURNS TABLE(col1 character varying, col2 character varying, col3 character varying)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000

AS $BODY$
BEGIN

-----Original Message-----
From: PetSerAl <petseral(at)gmail(dot)com>
Sent: Thursday, February 29, 2024 2:02 PM
To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: [EXTERNAL] Re: Record returning function accept not matched columns declaration

postgres=# with a(b) as (values (row(1,2,3))) postgres-# select * from a, coalesce(b);
ERROR: a column definition list is required for functions returning "record"
LINE 2: select * from a, coalesce(b);
^
postgres=#
postgres=# with a(b) as (values (row(1,2,3))) postgres-# select * from a, nullif(b, null);
ERROR: a column definition list is required for functions returning "record"
LINE 2: select * from a, nullif(b, null);
^
postgres=#
postgres=# with a(b) as (values (row(1,2,3))) postgres-# select * from a, unnest(array[b]);
ERROR: a column definition list is required for functions returning "record"
LINE 2: select * from a, unnest(array[b]);
^
postgres=#
postgres=# with a(b) as (values (row(1,2,3))) postgres-# select * from a, json_populate_record(b, null);
ERROR: a column definition list is required for functions returning "record"
LINE 2: select * from a, json_populate_record(b, null);
^
postgres=#

It seems PostgreSQL does not care about function being polymorphic, but only about return type being "record". It explicitly require column definition list in all this cases.

пт, 1 мар. 2024 г. в 00:32, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:
>
> On Thu, Feb 29, 2024 at 1:11 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>> > On Thursday, February 29, 2024, PetSerAl <petseral(at)gmail(dot)com> wrote:
>> >> postgres=# with a(b) as (values (row(1,2,3))) postgres-# select *
>> >> from a, coalesce(b) as c(d int,e int, f int) postgres-# union all
>> >> postgres-# select * from a, nullif(b, null) as c(d int,e int, f
>> >> int) postgres-# union all postgres-# select * from a,
>> >> unnest(array[b]) as c(d int,e int, f int) postgres-# union all
>> >> postgres-# select * from a, json_populate_record(b, null) as c(d
>> >> int,e int, f int); --expect OK
>>
>> > My concern with all of this is accepting the specification of
>> > column definitions for functions that don’t return the record pseudo-type.
>>
>> Hm? These cases all *do* return record, because that's what a.b is
>> typed as.
>
>
> I strongly dislike the seemingly overloaded terminology in this area. Namely I was trying to distinguish these two example function signatures.
>
> json_populate_record ( base anyelement, from_json json ) → anyelement
> jsonb_to_record ( jsonb ) → record
>
> Polymorphic functions do not require a column definition list. The non-polymorphic function signature does require the column definition list. That we accept a column definition list in the polymorphic case is settled code but seems like it led to this bug.
>
> David J.