Functions in Postgres

Functions
  Functions are reusable blocks of code that can accept parameters and return a value.
  Functions cannot modify data inside database but they can be used to perform calculations, display data etc Advantages:
  1. Code Reusability: Functions allow you to write code once and reuse it multiple times, reducing redundancy.
  2. Modularity: Functions help in breaking down complex tasks into smaller, manageable pieces.
  3. Improved Readability: Functions can make the code more readable and easier to understand.
  4. Performance: Functions can optimize performance by reducing the amount of data transferred between the database and application.
  5. Security: Functions can encapsulate sensitive logic and restrict direct access to underlying tables.

Examples

Print all data from employee table


CREATE OR REPLACE FUNCTION print_employee()
RETURNS TABLE (id INT, name TEXT, age INT, department TEXT) AS $$
BEGIN
    RETURN (SELECT * FROM employee);
END;
$$ LANGUAGE plpgsql;

-- Call as:
SELECT get_employee_count();

Call Function/Procedure from C++

C Code CPP Code 

$ apt-get install libpq-dev

#include <libpq-fe.h>
#include <iostream>

void call_postgres_function() {
    // Connect to database
    PGconn *conn = PQconnectdb("dbname=yourdb user=youruser password=yourpass host=localhost");
    
    if (PQstatus(conn) != CONNECTION_OK) {
        std::cerr << "Connection failed: " << PQerrorMessage(conn);
        PQfinish(conn);
        return;
    }

    // Call a FUNCTION (returns value)
    const char *func_query = "SELECT calculate_salary(123)"; // Replace with your function
    PGresult *func_res = PQexec(conn, func_query);
    
    if (PQresultStatus(func_res) != PGRES_TUPLES_OK) {
        std::cerr << "Function call failed: " << PQerrorMessage(conn);
    } else {
        char *result = PQgetvalue(func_res, 0, 0);
        std::cout << "Function returned: " << result << std::endl;
    }
    PQclear(func_res);

    // Call a PROCEDURE (no return value)
    const char *proc_query = "CALL update_salary(123, 10.0)"; // Replace with your procedure
    PGresult *proc_res = PQexec(conn, proc_query);
    
    if (PQresultStatus(proc_res) != PGRES_COMMAND_OK) {
        std::cerr << "Procedure call failed: " << PQerrorMessage(conn);
    } else {
        std::cout << "Procedure executed successfully" << std::endl;
    }
    PQclear(proc_res);

    PQfinish(conn);
}
          

$ sudo apt-get install libpqxx-dev

#include <pqxx/pqxx>
#include <iostream>

void call_postgres_with_libpqxx() {
    try {
        // Establish connection
        pqxx::connection conn("dbname=yourdb user=youruser password=yourpass host=localhost");
        
        // Call a FUNCTION
        pqxx::work txn1(conn);
        pqxx::result func_res = txn1.exec("SELECT calculate_salary(123)");
        txn1.commit();
        
        if (!func_res.empty()) {
            std::cout << "Function returned: " << func_res[0][0].as() << std::endl;
        }

        // Call a PROCEDURE
        pqxx::work txn2(conn);
        txn2.exec("CALL update_salary(123, 10.0)");
        txn2.commit();
        std::cout << "Procedure executed successfully" << std::endl;
        
    } catch (const std::exception &e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    }
}