How to use the result of the query within a function to execute UPDATE

Question

I'm very new to SQL and I'm currently using Postgres to execute a function. Essentially, I want the function to 1) first perform a query, 2) depending on the condition, update the specified field, and 3) return the result.

CREATE OR REPLACE FUNCTION get_overdue()
RETURNS TABLE (
    overdue boolean,
    due_date DATE
)
$$
BEGIN
SELECT overdue, due_date FROM booking;
IF NOW()::DATE > due_date::date then
-- I want to execute and return the following the query result: UPDATE booking SET overdue = true WHERE (the result of the above query) 
END IF;
end$$

klin · Accepted Answer · 2019-12-07 02:14:38Z

1

If I understand the logic well, you can do this in a single query:

update booking
set overdue = true
where due_date < current_date
returning *;

answered Dec 7, 2019 at 2:14

klin

123k15 gold badges241 silver badges263 bronze badges

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

Collectives™ on Stack Overflow

How to use the result of the query within a function to execute UPDATE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related