Computed fields not selected during INSERT

[dvv]

Environment

• PostgreSQL version: 16.1
• PostgREST version: 12.0.2
• Operating system: linux amd64

Description of issue

Given the schema:

-- table
create table if not exists app.office (
    id uuid not null primary key default uuid_generate_v7(), -- from pg_uuidv7, irrelevant
    name text not null
);
-- computed field
create or replace function app.created_at(app.office) returns timestamptz strict immutable return uuid_v7_to_timestamptz($1.id);

I fail to select computed fields just after successful insert:

POST /office?select=*,created_at
Prefer: missing=default, return=representation

{
  "name": "o1"
}

{
  "code": "42703",
  "details": null,
  "hint": null,
  "message": "column office.created_at does not exist"
}

while the following works just like expected:

###
POST /office?select=*
Prefer: missing=default, return=representation

{
  "name": "o1"
}
###
GET /office?name=eq.o1&select=*,created_at

[
  {
    "id": "018cecc4-0793-71fd-bf11-eb956fd48099",
    "name": "o1",
    "created_at": "2024-01-09T05:48:39.187+00:00"
  }
]

I tried to debug and obtained the stripped-down code:

with o as (
    insert into app.office (name) values ('o1') returning *
),
base as (
    select o.id, o.name, o.created_at from o -- NB: here `o` is somehow seen by the engine as _just a record_, not `app.office`
) select * from base;

Manual cast does the trick:

with o as (
    insert into app.office (name) values ('o1') returning *
),
base as (
    select o.id, o.name, (o::app.office).created_at from o
) select * from base;

TIA

[dvv]

Just another trial-and-error way to cast CTE while leaving base column selection intact:

with o as (
    insert into app.office as o (name) values ('o1') returning o
),
base as (
    select o.id, o.name, o.created_at from cast((select o from o) as app.office)
) select * from base;

[wolfgangwalther]

This is similar to #2688. In general, we should just not do returning * - and then try to add the computed columns later. Instead we should push the selection of columns all the way up to the first statement, no matter whether that's an INSERT statement or a function call. This makes sure that computed columns will always operate on the correct row type, without any casting.

I wonder whether the same workaround as in #2688 works here, too: Instead of querying select=*,comp does it work when you name the other columns explicitly? So select=a,b,c,comp?

[laurenceisla]

I tried to reproduce this issue to no avail at first. But the created_at name made me realize that it could be used for other tables too, so I overloaded it with other table as parameter and finally got the same error message. I think this is the same case here. E.g. from the original example:

-- table
create table if not exists app.office (
    id uuid not null primary key default uuid_generate_v7(), -- from pg_uuidv7, irrelevant
    name text not null
);
-- computed field (doesn't return an error here)
create or replace function app.created_at(app.office) returns timestamptz strict immutable return uuid_v7_to_timestamptz($1.id);
-- an overloaded computed field to other table reproduces the error
create or replace function app.created_at(app.todos) returns timestamptz strict immutable return uuid_v7_to_timestamptz($1.id);

@wolfgangwalther Maybe this is why #2688 couldn't be reproduced in the spec tests?

I wonder whether the same workaround as in https://github.com/PostgREST/postgrest/issues/2688 works here, too.

Yes, the example above does work by explicitly selecting the columns.

[dvv]

@laurenceisla right, that's my case, overloaded function for several tables.