PostgreSQL procedure: insert returning created id-CodePudding

I have this procedure:

create procedure get_users(
  name text
) language plpgsql AS $$
begin
   insert into "user" (
    "id",
    "name"
  ) values (
    uuid_generate_v4(),
    name
  );
end;
$$;

I wonder if it is possible to return the created id without using a function. That's because I try to use functions for queries and procedures for commands to comply with CQRS as much as possible.

Here is a fiddle: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=a8670a6809dcd38d75ef0b76efb96acd

CodePudding user response：

create procedure get_users
(
  name text
) 
language plpgsql AS $$
begin
    
   insert into "user" 
   ("id", "name") values 
   (uuid_generate_v4(), name) 
   returning "id", "name" into p_id, p_name
  
end;
$$;

P.S. p_id and p_name are declare variables.

CodePudding user response：

create procedure get_users
(
  name text
) 
language plpgsql AS $$
declare
    p_id text;
    p_name text; 
begin
    
   insert into "user" 
   ("id", "name") values 
   (uuid_generate_v4(), name) 
   returning "id", "name" into p_id, p_name;
  
end;
$$;

I don't know your table structure and field types. For examples, I used text type for same (p_id and p_name) variables.

CodePudding user response：

Yes, provided you have Postgres v11 or higher. You can declare an inout parameter (in V14 just need out parameter). Your insert can then return the created id into that parameter. Perhaps (see demo)

create or replace 
procedure establish_users(
    name_in text
 ,  inout user_id_ot uuid
 ) language plpgsql 
as $$
begin
   insert into "user" (name) 
   values (name_in)
   returning id into user_id_ot;
end;
$$;