How to create a postgres function that returns a value

All we need is an easy explanation of the problem, so here it is.

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('[email protected]', '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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

SQL Error [42601]: ERROR: query has no destination for result data

insert ... returning ... produces a result set that contains columns referenced in the returning clause. In a PL/pgSQL block that result set must be dealt with somehow. You have three options:

  • An intermediate variable

      CREATE OR REPLACE FUNCTION create_user(IN email EMAIL, password TEXT, thumb TEXT)
      RETURNS VARCHAR(40) AS 
      $BODY$
      DECLARE id VARCHAR(40);
      BEGIN
         insert into users (unqid, thumb, email, password) 
         values (gen_random_uuid(), thumb, email, password)
         returning unqid INTO id;
         RETURN id;
      END;
      $BODY$
        LANGUAGE plpgsql
        VOLATILE  
    
  • return query, which avoids an extra variable

      CREATE OR REPLACE FUNCTION create_user(IN email EMAIL, password TEXT, thumb TEXT)
      RETURNS TABLE (id VARCHAR(40)) AS 
      $BODY$
      BEGIN
         RETURN QUERY 
         insert into users (unqid, thumb, email, password) 
         values (gen_random_uuid(), thumb, email, password)
         returning unqid;
      END;
      $BODY$
        LANGUAGE plpgsql
        VOLATILE  
    
  • A simple SQL function, which I would prefer in your case as you don’t need any PL/pgSQL functionality as it stands

      CREATE OR REPLACE FUNCTION create_user(IN email EMAIL, password TEXT, thumb TEXT)
      RETURNS TABLE (id VARCHAR(40)) AS 
      $BODY$
         insert into users (unqid, thumb, email, password) 
         values (gen_random_uuid(), thumb, email, password)
         returning unqid;
      $BODY$
        LANGUAGE SQL
    

Method 2

A function basically replaces a fixed values when used as you do

so your code would look like

CREATE TABLE users (unqid varchar(64), thumb TEXT, email TEXT, password TEXT) 
CREATE OR REPLACE FUNCTION create_user(IN email TEXT, password TEXT, thumb TEXT)
RETURNS text AS 
$BODY$
DECLARE _unqid varchar(64);
BEGIN
   
 _unqid = gen_random_uuid();
   insert into users (unqid, thumb, email, password) 
   values (_unqid, thumb, email, password);
   RETURN  _unqid ;
END;
$BODY$
  LANGUAGE plpgsql
  VOLATILE
select create_user('[email protected]', 'passpopcorn', 'thumbelinaurl')
| create_user                          |
| :----------------------------------- |
| 759f4da9-bc17-4349-be4c-34e9b76fb78b |
SELECT * FROM users
unqid                                | thumb         | email            | password   
:----------------------------------- | :------------ | :--------------- | :----------
759f4da9-bc17-4349-be4c-34e9b76fb78b | thumbelinaurl | [email protected] | passpopcorn

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply