Insert values to table only if first Insert was successful

Question

Is there a way I could check the status of an insert query whether it was successful or not. I want to write to another table only if the first insertion was successful.

Below is the table I want to do the first insert and also it should return the conversation_id which I need for to make an insert to the second table.

Conversations table -

create table conversations (
 id uuid not null,
 conversation_id text UNIQUE PRIMARY KEY not null,
 participants jsonb not null,
 created_at timestamp with time zone,
 updated_at timestamp with time zone,
 avatar_url varchar(500),
 last_message varchar(32000)
)

After the successful insert on the first table, I should use the returned conversation_id and insert a record onto the messages table.After successful insertion it should return the value of the message field returned.

Messages Table -

create table messages (
 id uuid not null primary key,
 conversation_id text references conversations.conversation_id not null,
 message text,
 sender_id text,
 receiver_id text,
 message_type text,
 created_at timestamp with time zone DEFAULT now()
)

And after the two inserts I need to make an update to the first table again with the returned value of the message field.

Here is the full function to do the above, the below function works when calling using select, but when I call it through my client I get this error

-- Could not find the public.new_conversation(members, the_conversationId) function in the schema cache
CREATE OR REPLACE FUNCTION public.new_conversation(the_conversationId text, members jsonb, sent_message text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare
 conv_id text;
 the_message text;
BEGIN
 SELECT conversation_id into conv_id FROM conversations WHERE conversation_id = the_conversationId;
 if not found then
 insert into conversations(conversation_id, participants) values(the_conversationId,members) Returning conversation_id into conv_id;
 if found then
 insert into messages(conversation_id,message) values(conv_id,sent_message) Returning message into the_message;
 if found THEN
 update conversations set last_message = the_message where conversation_id = conv_id;
 end if; 
 end if;
 end if;
 return conv_id;
END;
$function$

Should I be doing any exceptions handling for my insert statements?

• mustaccio: This is commonly implemented using triggers.
• Sumchans: Is there anyway we could do a check inside of a function?
• Laurenz Albe: What is "the first insertion"? I see only one
• mustaccio: The status of that insert is determined by Postgres not throwing an error, so, in the absence of a handler, if execution gets to the next statement it means the previous statement was successful.
• Erwin Brandstetter: There are better solutions. But the best solution depends on a couple of key questions: 1. Can there be concurrent writes to the table? 2. Do you always insert the hard wired constants, or do you want to pass parameters? 3. Why the different data types for conv_id and new_conv_id? (Seems like both should be integer, as well as the return type.) 4. You mention writing to another table, but there is none in your code. Do you want to write the resulting ID from the first SELECT or INSERT? 5. Do you actually need a function? A single query with data-modifying CTEs might do the job.

Answer 1

Assuming no concurrent writes on the involved tables, this simpler function should cover everything you describe:

CREATE OR REPLACE FUNCTION public.new_conversation(INOUT _conv_id text, _members jsonb, _sent_message text)
 LANGUAGE plpgsql AS
$func$
BEGIN
 IF NOT EXISTS (SELECT FROM conversations WHERE conversation_id = _conv_id) THEN
 INSERT INTO conversations (conversation_id, participants, last_message)
 VALUES (_conv_id, _members, _sent_message);
 INSERT INTO messages (conversation_id, message)
 VALUES (_conv_id, _sent_message);
 END IF;
END
$func$

db<>fiddle here

Most importantly, don't INSERT and later UPDATE the same row, when you can INSERT all at once.

I use an INOUT parameter, meaning the input for _conv_id will be the output, because I don't (need to) alter it anyway.

I don't see anything that would require EXCEPTION handling. If any exception occurs the whole function (which is always executed inside an outer transaction and hence atomic!) is rolled back.

The only remaining riddle is the role of the id columns. Since those are NOT NULL, you need to write to them unless they have a default.

Aside: PRIMARY KEY makes the column UNIQUE NOT NULL implicitly, this:

conversation_id text UNIQUE PRIMARY KEY not null,

should just be:

conversation_id text PRIMARY KEY,

If there can be concurrent writes, things are not as simple. See:

• Is SELECT or INSERT in a function prone to race conditions?

• Sumchans: Thanks Erwin for the detailed explanation.