Обсуждение: Appetite for syntactic sugar to match result set columns to UDT fields?

I could not see anything on this subject but would like to know if there would be any appetite for this kind of feature.

I am happy to flesh out more details if they were likely to be deemed worth implementing.

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.

This is fine for simple cases.

When the number of columns grows large and the code grows old this can become risky to maintain. Trusting that the order will always match and that someone wont accidentally move columns seems risky to me.

The Solution

Some syntax like:

    SELECT CAST((F1=> value1, F2 => value2) AS FOO BY NAME)

or

    SELECT FOO(F1 => VALUE1, F2=> value2);

or some other well-defined and non-conflicting syntax.

The Solution

Some syntax like:

    SELECT CAST((F1=> value1, F2 => value2) AS FOO BY NAME)

or

    SELECT FOO(F1 => VALUE1, F2=> value2);

or some other well-defined and non-conflicting syntax.

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

 @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

Philip Warner <pjw@rhyme.com.au> writes:
> I'd like a formulation like: 
>         Select Row(T1.T1F7 as F1, T2.T2F3 as F2, Tp.Fq as Fn)::FOO By Name
> Or 
>         Select FOO(F1:=T1.T1F7, F2:=T2.T2F3, Fn:=Tp.Fq)

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

Well, you can build a real function call that does that:

CREATE FUNCTION FOO(F1 int, F2 int, ...) RETURNS FOO AS ...;

SELECT FOO(F1 => T1.T1F7, F2 => T2.T2F3, ...) FROM ...;

Admittedly you have to get the ROW() constructor right in the
body of function FOO, but then you've got it.  This approach
also lets you insert appropriate default values for unspecified
columns, which is a feature we surely wouldn't build in if this
were wired-in syntax.

            regards, tom lane

While you are correct that the structure will create a UDT (which is how I framed a possible solution to the larger problem in my second message), the example:

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;

Becomes quite ugly (and perhaps inefficient?):

Begin
    ...

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

The (FOO(F1:=T1.T1F7, F2:=T2.T2F3), ..., Fn:=Tp.Fq)).* looks ugly, at least, and it required because of the return type.

Perhaps I need to go back to the original "syntactic sugar" concept so that we can achieve a "correct" row structure when the code is parsed, not by calling runtime functions (constructors)?

They key here is that we know the type that needs to be returned (or we can specify it in SQL somehow); it would be good if the parser could perform the tasks to make sure that the row structure matched the type requirement.

On 2025-09-06 00:08, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

I'd like a formulation like:
        Select Row(T1.T1F7 as F1, T2.T2F3 as F2, Tp.Fq as Fn)::FOO By Name
Or
        Select FOO(F1:=T1.T1F7, F2:=T2.T2F3, Fn:=Tp.Fq)

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

Well, you can build a real function call that does that:

CREATE FUNCTION FOO(F1 int, F2 int, ...) RETURNS FOO AS ...;

SELECT FOO(F1 => T1.T1F7, F2 => T2.T2F3, ...) FROM ...;

Admittedly you have to get the ROW() constructor right in the
body of function FOO, but then you've got it.  This approach
also lets you insert appropriate default values for unspecified
columns, which is a feature we surely wouldn't build in if this
were wired-in syntax.

            regards, tom lane