I need to query data from a jobs table and insert into a clients table, into two different columns.

First I grab the customer name to put on name column, then I grab the list of unpaid/partially paid jobs of the customer as an array of objects to put on the open_jobs column.

The code below works but it only returns the first row.

do $$
  declare
    x varchar;
    y json;
  begin
    select distinct customer
    into x
    from jobs
    order by customer asc;

    select array_to_json(array_agg(row_to_json(t)))
    from (
      select job_location, job_total, paid, invoice_no
      into y
      from jobs
      where customer = x and paid < job_total
      order by invoice_no asc
    )t;

    insert into clients(name, open_jobs)
    values(x, y);
  end;
$$ language plpgsql;

I put a link with an image of the query result for reference.

Query Result

CodePudding user response：

To elaborate on my comment(not tested):

do $$
  declare
    x varchar;
    y json;
  begin
    FOR x IN select distinct customer from jobs
    order by customer asc LOOP

       select array_to_json(array_agg(row_to_json(t)))
       from (
        select job_location, job_total, paid, invoice_no
        into y
        from jobs
        where customer = x and paid < job_total
        order by invoice_no asc
      )t;

      insert into clients(name, open_jobs)
      values(x, y);
   END LOOP;
  end;
$$ language plpgsql;

Your original function will only select the first value returned by the customer query. By using the FOR x IN ... LOOP ... END LOOP you iterate over all the results of the query.