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
   );

2. 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();

3. 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
   . . .
   . . .
   );

4. 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