Postgres declaration type for multiple rows

Question

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;

Mike Organek · Accepted Answer · 2020-08-11 17:33:52Z

1

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;

answered Aug 11, 2020 at 17:33

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kevzettler Over a year ago

This works for this specific use case but doesn't answer the more general question of how to declare a variable for a set or multiple rows. Which I imagine I will have to ask again in the future.

Mike Organek Over a year ago

@kevzettler See here: stackoverflow.com/a/22306979/13808319

kevzettler Over a year ago

also this query seems to throw an error:

{"hint":"If you want to discard the results of a SELECT, use PERFORM instead.","details":null,"code":"42601","message":"query has no destination for result data"}

Mike Organek Over a year ago

@kevzettler Did you specify language sql?

Collectives™ on Stack Overflow

Postgres declaration type for multiple rows

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related