CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresstored-functions
kevzettler
kevzettler

Reputation: 5213

Postgres declaration type for multiple rows

I have a postgres function that uses a %ROWTYPE declaration. When I execute this function I get a failure about returning more than one row "code":"P0003","message":"query returned more than one row"

I can successfully execute the query without the returning * into assignments; statement. Which leads me to believe the assignments data.assignment%ROWTYPE; is only for one row?

create or replace function assign(order_id int) returns json as $$
declare
 assignments data.assignment%ROWTYPE;
begin
        insert into data.assignment
        (order_item_id, pharmacy_id)
         (
               select oi.id as order_item_id, mci.pharmacy_id
               from api.order_items as oi
               inner join api.min_cost_inventory_items as mci on mci.drug_id = oi.drug_id
               where oi.order_id = $1
         )
        returning * into assignments;

        return json_build_object(
          'assignments', assignments
        );
end
$$ security definer language plpgsql;
revoke all privileges on function assign(int) from public;

Upvotes: 0

Views: 985

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

Yes. The %ROWTYPE is not strictly necessary, but in any case it holds only one row. You could make an array of data.assignment[], but then you need to loop to fill it.

You can simplify the function as follows:

create or replace function assign(_order_id int) returns jsonb as $$
  with i as (
    insert into data.assignment
     (order_item_id, pharmacy_id)
    select oi.id as order_item_id, mci.pharmacy_id
      from api.order_items as oi
           inner join api.min_cost_inventory_items as mci 
                   on mci.drug_id = oi.drug_id
                where oi.order_id = _order_id
    returning *
  )
  select jsonb_agg(to_jsonb(i)) from i;
$$ security definer language sql;

Upvotes: 1

Related Questions