CODEWITHSUNDEEP
postgresql
Saad
Saad

Reputation: 28486

how to get single row in plpgsql?

I'm querying to see if there is an end time if there isn't then I want to update it to include the current time. However, I need to store if the end time exists and the id of the row to update later on with, but I'm not quite sure how to go about doing this. 

endTime := now();
strresult := 'select end_time from process order by start_time desc limit 1';
IF strresult = '' THEN
    UPDATE tasks SET end_time = endTime WHERE id = ???;
END IF;

Upvotes: 0

Views: 40

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45770

You are asking on SELECT INTO statement. But you don't need it. You should not to save a current time to variable - inside transaction the time is not moving. Your code is little bit messy - you are searching a situation without opened process, then you close a tasks.

IF NOT EXISTS(SELECT *
                 FROM process
                 WHERE end_time IS NULL)
THEN
   UPDATE tasks SET end_time = CURRENT_TIME
     WHERE end_time IS NULL;
END IF;

Upvotes: 1

Related Questions