Create or replace function syntax

Create function

Create postgresql function

Create or replace function with parameters. Syntax and examples.

CREATE OR REPLACE FUNCTION 
set_customers(in p_first_name varchar(50), in p_last_name varchar(50)) 
RETURNS varchar AS $$
DECLARE
  v_result varchar(250):='Customer added';
  v_count numeric:=0;
BEGIN
  SELECT count(*) into v_count 
  FROM customers 
  WHERE first_name = p_first_name 
  AND last_name = p_last_name;
  if v_count>0 then
   	v_result:='Customer allready exist';
	return v_result;
  else
	insert into customers(first_name, last_name) 
    values (p_first_name, p_last_name);	
	return v_result;
  end if;	
EXCEPTION 
  when others then
	begin
	 v_result := 'Database error!';
	 return v_result;
	end;	
END;
$$ LANGUAGE 'plpgsql' ;  

Create postgresql function with OUT parameters

CREATE OR REPLACE FUNCTION 
get_customers(in p_first_name varchar, out p_id numeric, 
out p_last_name varchar, out p_reg_date date) 
RETURNS record AS $$
DECLARE
BEGIN
  SELECT id, last_name, reg_date 
  INTO p_id, p_last_name, p_reg_date
  FROM customers 
  WHERE first_name = p_first_name;
RETURN;
END;
$$ LANGUAGE 'plpgsql' ;

Alter Function |
Drop Function