Giriş
Söz dizimi şöyle
CREATE OR REPLACE FUNCTION function_name(parameter_type parameter_name, ...)
RETURNS return_type AS $$
BEGIN
-- Function logic
END;
$$ LANGUAGE plpgsql;1. Scalar Functions
Açıklaması şöyle
Scalar functions are user-defined functions that return a single value. They can take one or more input parameters and execute SQL statements.
Örnek
Şöyle yaparız.
CREATE FUNCTION myfunction(arg1 INTEGER, arg2 TEXT) RETURNS INTEGER
AS $$
DECLARE result INTEGER;
BEGIN
SELECT COUNT(*) INTO result FROM mytable WHERE mycolumn = arg2;
RETURN result * arg1;
END;
$$ LANGUAGE plpgsql;Örnek
Şöyle yaparız
-- Create a simple function that returns the sum of two integers
CREATE OR REPLACE FUNCTION add_two_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- Call the function
SELECT add_two_numbers(5, 7); -- Returns 12Açıklaması şöyle
In the above example, we create a function add_two_numbers that takes two integers as input parameters and returns their sum. We use the CREATE FUNCTION statement with the RETURNS keyword to specify the return type.
Örnek
Şöyle yaparız. Verilen parametreleri tabloya ekler
3. Aggregation Functions
CREATE OR REPLACE FUNCTION MyInsert(balance NUMERIC(19), id VARCHAR(250)) RETURNS VOID
AS
$BODY$
BEGIN
INSERT INTO balance(balance, invoice_items_id) VALUES (balance , id);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;2. Table-Valued Functions
Açıklaması şöyle
Table-valued functions return a set of rows as a result. You can think of them as producing a virtual table.
Örnek
Şöyle yaparız
-- Create a table-valued function that returns all employees in a department
CREATE OR REPLACE FUNCTION get_employees_in_department(dept_id INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
RETURN QUERY SELECT employee_id, employee_name
FROM employees WHERE department_id = dept_id;
END;
$$ LANGUAGE plpgsql;
-- Call the function
-- Returns all employees in department 101
SELECT * FROM get_employees_in_department(101); Açıklaması şöyle
In this example, we create a function get_employees_in_department that takes a department ID as input and returns a table of employees in that department. We use the RETURNS TABLE clause to specify the return type.
Açıklaması şöyle
Aggregation functions are used to perform operations on sets of values and return a single result. PostgreSQL allows you to create custom aggregation functions to suit your specific needs.
Örnek
Şöyle yaparız
-- Create an aggregation function that calculates the median
CREATE OR REPLACE FUNCTION median_accumulator(sfunc INTERNAL, stype INTERNAL,
finalfunc INTERNAL)
RETURNS internal STRICT
LANGUAGE C;
-- Define the final function
CREATE OR REPLACE FUNCTION median_finalfunc(internal) RETURNS double precision
LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT CASE
WHEN $1 IS NULL THEN NULL
ELSE (percentile_cont(0.5) WITHIN GROUP (ORDER BY $1))
END;
$$;
-- Create an aggregate that uses the function
CREATE AGGREGATE median(double precision) (
SFUNC = median_accumulator,
STYPE = internal,
FINALFUNC = median_finalfunc
);Açıklaması şöyle
In this example, we create a custom aggregation function for calculating the median of a set of values. Custom aggregations give you the flexibility to extend PostgreSQL’s built-in aggregation functions.
