Part 116: Table-Valued Functions (TVF): Returning Dynamic Result Sets

A function that returns an entire set of rows and columns, usable in the FROM clause.

1. PostgreSQL Syntax

CREATE OR REPLACE FUNCTION get_top_products_by_category(cat_name VARCHAR, top_limit INT)
RETURNS TABLE (product_id BIGINT, product_name VARCHAR, price DECIMAL) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name, products.price
  FROM products
  WHERE category = cat_name
  ORDER BY products.price DESC
  LIMIT top_limit;
END;
$$ LANGUAGE plpgsql;

-- Calling the function in the FROM clause:
SELECT * FROM get_top_products_by_category('Electronics', 5);

Key Takeaways

• Table-valued functions act like parameterized views.

In Part 117, we explore Stored Procedures: Parameters, Logic & Execution!