PERFORM in the PostgreSQL world is like that tough, silent hero who shows up, gets the job done, and disappears without leaving any trace in the form of returned data. You use this command when you want to run an SQL query inside a PL/pgSQL function, but you don't care about processing or saving the result. The main job of PERFORM is to call a query so something happens—like modifying data or calling another function—not to get a result back.
PERFORM is used when you don't need the result of a query. Unlike a regular SELECT, which expects you to do something with the result, PERFORM just runs the query and quietly moves on. This is especially handy if you're calling a function just for its side effect, not for what it returns. For example, to write something to a log. This approach makes your code simpler and clearer: less clutter, more meaning.
Examples of Using PERFORM
Calling Functions
Let's break it down with a real example. Imagine we have a function called log_action that writes info about user actions to the logs. This function doesn't return anything, and we just want it to do its thing. Here's how you do it with PERFORM:
CREATE OR REPLACE FUNCTION log_action(user_id INT, action TEXT) RETURNS VOID AS $$
BEGIN
INSERT INTO logs (user_id, action, log_time)
VALUES (user_id, action, NOW());
END;
$$ LANGUAGE plpgsql;
-- Now we use PERFORM to call this function:
PERFORM log_action(5, 'User logged in');
What's happening here? The PERFORM command calls the log_action function, which adds a record to the logs table. Make sure you get this: the result of the function call is ignored. We're using it only for its effect, not for any returned value.
Updating Data
Sometimes PERFORM is useful when you need to run a query to change data and you don't care about the result. For example, let's update the status of an order in the orders table.
CREATE OR REPLACE FUNCTION update_order_status(order_id INT, new_status TEXT) RETURNS VOID AS $$
BEGIN
UPDATE orders
SET status = new_status
WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;
-- Use PERFORM to call this function:
PERFORM update_order_status(101, 'Shipped');Here, update_order_status updates the status of the order with ID 101. We don't care about the result of the SQL query inside the function, so PERFORM is the perfect choice.
Running Helper Operations
Sometimes functions have mini-operations, just "helper" logic that helps finish a complex process. Let's say we want to clear the cache after updating a table:
CREATE OR REPLACE FUNCTION clear_cache() RETURNS VOID AS $$
BEGIN
DELETE FROM cache_table;
END;
$$ LANGUAGE plpgsql;
-- Call it in another function:
CREATE OR REPLACE FUNCTION update_product(product_id INT, new_price NUMERIC) RETURNS VOID AS $$
BEGIN
UPDATE products
SET price = new_price
WHERE id = product_id;
-- Clear the cache after changing data:
PERFORM clear_cache();
END;
$$ LANGUAGE plpgsql;Here's the magic: you can chain actions together, using PERFORM to call functions whose results you totally don't care about.
Practical Tasks
Let's check out a few examples of how PERFORM can make a developer's life easier.
Example 1: Logging Procedure Steps
Say you have a complex payment processing procedure, and you need to track each stage by writing it to a log. We can define a log_stage function to write info, then use PERFORM:
CREATE OR REPLACE FUNCTION log_stage(stage_name TEXT) RETURNS VOID AS $$
BEGIN
INSERT INTO process_logs(stage, log_time)
VALUES (stage_name, NOW());
END;
$$ LANGUAGE plpgsql;
-- And here's an example procedure:
CREATE OR REPLACE FUNCTION process_payment(payment_id INT) RETURNS VOID AS $$
BEGIN
-- Log the start
PERFORM log_stage('Start payment processing');
-- Do the first step
UPDATE payments
SET status = 'Processing'
WHERE id = payment_id;
PERFORM log_stage('Updated payment status');
-- Do the final step
UPDATE payments
SET status = 'Completed'
WHERE id = payment_id;
PERFORM log_stage('Payment completed');
END;
$$ LANGUAGE plpgsql;Here, log_stage is called via PERFORM to record the state at each step of the procedure. This makes your code easier to debug.
Example 2: Triggering Notifications
Imagine you have a notification system, and you need to send a notification after every important action. PERFORM can be used to call the function responsible for this:
CREATE OR REPLACE FUNCTION send_notification(user_id INT, message TEXT) RETURNS VOID AS $$
BEGIN
INSERT INTO notifications (user_id, message, created_at)
VALUES (user_id, message, NOW());
END;
$$ LANGUAGE plpgsql;
-- Use it in a procedure:
CREATE OR REPLACE FUNCTION complete_task(task_id INT) RETURNS VOID AS $$
DECLARE
user_id INT;
BEGIN
-- Get the task author
SELECT assigned_to INTO user_id
FROM tasks
WHERE id = task_id;
-- Complete the task
UPDATE tasks
SET status = 'Completed'
WHERE id = task_id;
-- Send the notification
PERFORM send_notification(user_id, 'Your task has been completed');
END;
$$ LANGUAGE plpgsql;Here, PERFORM lets you focus only on the side effect—sending the notification—while ignoring the function's return value.
Useful Tips and Common Mistakes
When you use PERFORM, it's important to remember a few things. For example, PERFORM doesn't check if the query returned any data. That means if the result of a function or SQL query is important for your logic, you should use SELECT INTO instead. Check out this example:
-- Potential mistake
PERFORM some_function_that_must_return_value();
-- The fix
SELECT some_function_that_must_return_value() INTO some_variable;Another common mistake is using PERFORM where you actually need the result of the query, like for data validation. In those cases, of course, you should get the result and check it.
In real projects, the PERFORM command helps make functions and procedures simpler, easier to read, and easier to debug. Combined with logging (RAISE NOTICE) and built-in PostgreSQL diagnostic functions like current_query(), it becomes a key tool for building reliable, manageable, and understandable systems.
GO TO FULL VERSION