2

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?

3

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
    
2
3

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('newuser@mail.com', 'passpopcorn', 'thumbelinaurl')
| create_user                          |
| :----------------------------------- |
| 759f4da9-bc17-4349-be4c-34e9b76fb78b |
SELECT * FROM users
unqid                                | thumb         | email            | password   
:----------------------------------- | :------------ | :--------------- | :----------
759f4da9-bc17-4349-be4c-34e9b76fb78b | thumbelinaurl | newuser@mail.com | passpopcorn

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.