Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Writing User-Defined Functions in PostgreSQL

Introduction
Side-effects in functions
7. Multiple operations in one function

Instruction

Typically, functions are more complex and can contain multiple INSERT, UPDATE, and/or DELETE operations, as shown in the example below:

CREATE FUNCTION remove_product(prod_id smallint)
RETURNS void AS $$
BEGIN
  UPDATE product
  SET
    is_available = false,
    is_active = false
  WHERE product_id = prod_id;

  DELETE FROM prod_inventory
  WHERE product_id = prod_id;
END;
$$ LANGUAGE plpgsql;

The function above updates a product with a given ID and sets its attributes (is_available and is_active) to false. It also deletes the product from the product inventory.

Exercise

Implement a new function named remove_user_account() that has one argument: user_account_id, of type integer.

This function will do the following operations:

  1. Update the record in the user_account table with a given ID (user_account_id), set the is_deleted flag to true, and set the deleted_date to the current_date value.
  2. Mark all users' posts as deleted: update all user's posts (based on a given ID) in the post table, set the post's is_deleted flag to true, and set the deleted_timestamp to the current_timestamp value.