I am trying to move some of my web app logic into postgres functions. But I am hitting some errors while creating a very basic insert function.
This is the function that I am trying to create;
CREATE OR REPLACE FUNCTION create_user(IN email EMAIL, password TEXT, thumb TEXT)
RETURNS text AS
$BODY$
BEGIN
insert into users (unqid, thumb, email, password)
values (gen_random_uuid(), thumb, email, password)
returning unqid ;
END;
$BODY$
LANGUAGE plpgsql
VOLATILE
I am trying to get the function to return the uuid of the item if the insert is successful. And I am calling it like this;
select * from create_user('newuser@mail.com', 'passpopcorn', 'thumbelinaurl');
Getting this error;
SQL Error [42601]: ERROR: query has no destination for result data
Where: PL/pgSQL function create_user(email,text,text) line 3 at SQL statement
From my googling it sounds like this error comes up when you don't have a return statement in the query. But in my Insert query, I do have a returning statement.
Extra question; for simple insert statements (like this one here, or one with a couple of select followed by an insert), would functions be a better bet or procedures?