Re: Appetite for syntactic sugar to match result set columns to UDT fields?

 @Tom Lane Yes, a good question. I abstracted my example to the point of
meaninglessness. A more concreate example:  

Create Type FOO(
    F1 Int,
    F2 Int,
    ...
    Fn Int) 

Create Function GET_SOMETHING(...) Returns SetOf FOO
    Language PLPGSQL
...
Begin
    ... 

    Return Query
        Select T1.T1F7 as F1, T2.T2F3 as F2, Tp.Fq as Fn
        From T1 Join T2 On...Join...Tp
        Where...
    ...
End; 

This first example does not need the " as Fn" statements, they are just
illustrative. The key problem here is that one needs to be certain that
the order of the fields exactly matched the return type definition.  

Another example would be a function in PLPGSQL that contains a loop: 

    Declare
        _REC FOO; 

    Begin
        for _REC In
            Select ...name-based-constructor... 
        From T1 Join T2 On...Join...Tp
        Where... 

I'd like a formulation like: 

        Select Row(T1.T1F7 as F1, T2.T2F3 as F2, Tp.Fq as Fn)::FOO By
Name
        From T1 Join T2 On...Join...Tp
        Where... 

Or 

        Select FOO(F1:=T1.T1F7, F2:=T2.T2F3, Fn:=Tp.Fq)
        From T1 Join T2 On...Join...Tp
        Where... 

Or any other syntax that can be consistent, not break function calling
etc 

Basically: it's some form of UDT constructor with named parameters,
whether by cast, pseudo function call or some other mechanism. 

This also allows plain SQL to return UDTs reliably and consistently. 

I really hope these example makes the intent clearer! 

On 2025-09-05 15:54, Tom Lane wrote:

> Philip Warner <pjw@rhyme.com.au> writes: 
> 
>> The Problem 
>> Currently, if one has: 
>> Create Type FOO(
>> VALUE1 Int,
>> VALUE2 Int); 
>> And one has a query: 
>> Select F1, F2 from A_TABLE; 
>> One can return the rows, or one can create a row object and cast it to
>> FOO type.
> 
> I'm kind of wondering where is the connection between type FOO and
> table A_TABLE?
> 
> Once you have the table, there is already a perfectly good composite
> type A_TABLE that you could use without any worries about whether it
> matches the table.  So I'm not following why introducing FOO adds
> anything of value.
> 
> regards, tom lane