PostgreSQL自定义函数使用指南：带双IN参数的函数查询调用方法

Hey there! Let's walk through how to work with custom functions in PostgreSQL—starting with the basics, then focusing on calling the two-IN-parameter function you've already built.

When working with custom functions, keep these core points in mind:

• Define clearly: Use the CREATE FUNCTION statement to specify your function's name, parameters (with their modes like IN, OUT, or INOUT), return type, and the function body (written in SQL, PL/pgSQL, or other supported languages).
• Match types always: Make sure the data types of values you pass match exactly what the function expects—mismatches will throw errors.
• Check function details: In psql, run \df your_function_name to quickly view the function's parameters, return type, and definition if you need a reminder.

Let's use a concrete example to make this tangible. Suppose you've created a function like this (adjust to match your actual function):

-- Example function: calculates total cost from price and quantity
CREATE FUNCTION get_total_cost(item_price NUMERIC, item_quantity INTEGER)
RETURNS NUMERIC
LANGUAGE SQL
AS $$
SELECT item_price * item_quantity;
$$;

Here are the most common ways to call it in queries:

1. Positional Parameter Call (Most Common) #

Just pass values in the same order as the function's defined parameters. This is straightforward for small parameter lists:

-- Direct value call
SELECT get_total_cost(29.99, 3); -- Returns 89.97

-- Call alongside table data
SELECT product_id, product_name, get_total_cost(unit_price, stock_count) AS total_inventory_value
FROM products;

2. Named Parameter Call (More Readable) #

If you want to avoid relying on parameter order (great for functions with more parameters later), specify the parameter names explicitly:

-- Direct value call (order doesn't matter here)
SELECT get_total_cost(item_quantity := 3, item_price := 29.99);

-- Call with table data
SELECT product_id, product_name, get_total_cost(item_price := unit_price, item_quantity := stock_count) AS total_inventory_value
FROM products;

3. Use in Filter Clauses #

You can also use the function's output to filter results in a WHERE clause:

-- Get products where total inventory value exceeds $500
SELECT product_id, product_name
FROM products
WHERE get_total_cost(unit_price, stock_count) > 500;

Bonus: Handling Set-Returning Functions #

If your function returns multiple rows (e.g., RETURNS SETOF some_type), call it with a SELECT * FROM clause:

-- Example for a set-returning function
SELECT * FROM get_product_details(category_id := 1, min_price := 10);

内容的提问来源于stack exchange，提问作者Himanshu Tiwari