PostgreSQL – Create Function example

Example 1: Function to return the row count of a table:

CREATE FUNCTION get_row_count(v_table)
RETURNS int
AS
$$
DECLARE
v_count int; 
select count(*) into v_count from v_table;
 RETURN v_count;
END;
$$
LANGUAGE plpgsql;

Call the function as below where table_name is the name of the table for which you would like to return the row count.

SELECT get_row_count(table_name);

Example 2: Function to return the person name for an id:

CREATE FUNCTION get_person_name(v_id)
RETURNS varchar(50)
AS
$$
DECLARE
v_name varchar(50); 
select first_name||' '||last_name into v_name from person where id=v_id;
 RETURN v_name;
END;
$$
LANGUAGE plpgsql;

Call function as below where ‘id’ is the employee id of the employee that you would like to return the name for.

SELECT get_person_name(id);