field/object not populated on insert using trigger

[leefordjudes]

Describe the bug
i create customer using insertIntoCustomerCollection with data,
In database i create an account for this customer using before insert trigger,
then i assign account id to this customer record.

here i got account id, but pg-graphql wont populate account table.

it is populated on update & get.

To Reproduce
Steps to reproduce the behavior:

1. create table customer

create table if not exists customer
(
    id                       int              not null generated always as identity primary key,
    name                     text             not null,
   . . .
  . . .
    tracking_account         boolean          not null default false,
    credit_account           int
);

3. create trigger:

create trigger create_update_customer_account
    before insert or update
    on customer
    for each row
    when (new.tracking_account)
execute procedure create_update_credit_account();

4. create account table:

create table if not exists account
(
    id                       int       not null GENERATED BY default as identity (start with 101 increment by 1) primary key,
    name                     text      not null,
    account_type             text      not null
. . .
. . .
);

6. create function for above trigger

create function create_update_credit_account()
    returns trigger as
$$
declare
  . . .
begin
    if tg_op = 'INSERT' then
        . . .
        insert into account(name, account_type, . . . tracked)
        values (new.name, acc_type, . . .,true)
        returning id into new.credit_account;
    else
        update account
        set name                     = new.name,
            . . .
           . . .
        where id = new.credit_account;
    end if;
    return new;
end;
$$ language plpgsql;

Expected behavior
it should return object created via before insert trigger

Screenshots
create customer:

get customer:

Versions:

• PostgreSQL: 16
• pg_graphql: 1.5.5

[olirice]

Here's a complete reproducible example using SQL

create table if not exists customer (
    id int not null generated always as identity primary key,
    name text not null
);

create table if not exists account (
    id int primary key
);

create or replace function create_update_credit_account()
    returns trigger as
$$
declare

begin
    if tg_op = 'INSERT' then
        insert into account(id)
        values (new.id);
    end if;
    return new;
end;
$$ language plpgsql;

create trigger create_update_customer_account
    before insert or update
    on customer
    for each row
execute procedure create_update_credit_account();

-- This is roughly what the GraphQL query turns into
with affected as (
    insert into customer(name)
    values ('foo')
    returning id, name
)
select
    aff.id,
    aff.name,
    acc.id as account_id
from
    affected aff
    left join account acc
      on aff.id = acc.id

Returns:

id	name	account_id
1	foo	null

So unfortunately this is a limitation in SQL and not something we can fix in pg_graphql

If anyone is aware of a way to restructure the SQL to be able to return the account record from an insert statement please let me know