PostgreSQL: How do I have my function return the id of the new row I inserted?

Question

I'm new to sql

This is my error

SQL Error [42601]: ERROR: syntax error at or near "("

CREATE OR REPLACE FUNCTION func_add_reimbursement_ticket( f_employee text, f_amount decimal, f_request_type text, f_description text )
    RETURNS int as 
$id_num$
  DECLARE 
      id_num int = null; 
  BEGIN 
      SELECT username FROM employee WHERE username = f_employee; 
      IF NOT FOUND THEN 
          id_num := -1; 
      else 
          SET id_num TO (INSERT INTO reimbursement_ticket (employee, amount, request_type, description)
       VALUES (f_employee, f_amount, f_request_type, f_description) RETURNING id); 
      END IF; 
  RETURN (id_num); 
END;
$id_num$ 
LANGAUGE plpgsql;

Answer 1

There are multiple errors in your code. First LANGAUGE needs to be LANGUAGE.

Variables are assigned using :=, not set.

But if you want to store the result of a query into a variable, you need to use the INTO clause.

The result of a SELECT must be stored somewhere. If you don't want that, you need to use perform. Alternatively you can just use if not exists

Putting that all together, the function should look something like this:

CREATE OR REPLACE FUNCTION func_add_reimbursement_ticket( f_employee text, f_amount decimal, f_request_type text, f_description text )
    RETURNS int 
as 
$id_num$
DECLARE 
  id_num int; 
BEGIN 
  if not exists (select * FROM employee WHERE username = f_employee) then
    return -1;
  end if;

  INSERT INTO reimbursement_ticket (employee, amount, request_type, description)
  VALUES (f_employee, f_amount, f_request_type, f_description) 
  RETURNING id into id_num;

  RETURN id_num; 
END;
$id_num$ 
LANGUAGE plpgsql;