CREATE OR REPLACE FUNCTION

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 12

Açı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

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.

3. Aggregation Functions

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.