PostgresSQL Functions

Most databases support user-defined functions. Relational databases, such as Postgres and Oracle, provide this functionality through “Create Function” SQL statements.

The version of Postgres used here is 12.4. First, we connect to Postgres database.

$> psql -h [server] -p [port] -U [user]

Now, we are in Postgres command prompt. Connect to a database you want to use.

# \connect [database]

Now, write a sql file (e.g., function.sql) that has function SQL statements. This function returns the number of rows in ‘users’ table.

CREATE OR REPLACE FUNCTION f_example()
    RETURNS int
    LANGUAGE plpgsql
AS
$$
declare
    row_count  integer;
BEGIN
    SELECT count(*)
      INTO row_count
      FROM users;
    RETURN row_count;
END;
$$;

Now you can execute this file.

# \i function.sql

Here, we assume that there is a table named ‘users’. Now, you can use ‘f_example’ function.

# SELECT f_example();

To list functions, we can use the following SQL query statement.

SELECT specific_catalog, routine_name, external_language
  FROM information_schema.routines
 WHERE routine_type='FUNCTION'
   AND specific_schema ='public';

To delete a function, use DROP FUNCTION statement. Postgres treats functions with same name but different parameters as different functions…