CODEWITHSUNDEEP
postgresqlplpgsql
仝宣昌
仝宣昌

Reputation: 23

"ERROR: query has no destination for result data" in PL/pgSQL function

I want to create a function which can be used to insert data into the table uuser and return the uid with data type serial.

CREATE TABLE uuser (
  uid serial PRIMARY KEY
, user_name text NOT NULL
, last_login timestamptz NOT NULL DEFAULT current_timestamp
, real_name text
, age int
, city text
, CHECK (age > 0 and age < 140)
);

CREATE OR REPLACE FUNCTION ins_uuser(p_user_name text
                                   , p_real_name text
                                   , p_age int
                                   , p_city text)
RETURNS integer AS
$$
BEGIN
INSERT INTO uuser(user_name, last_login, real_name, age, city)
VALUES($1, CURRENT_TIMESTAMP, $2, $3, $4)
RETURNING uid;
END;
$$
LANGUAGE plpgsql VOLATILE;

SELECT ins_uuser('Joker', 'Jack', 18, 'New York') AS uuser_id;

ERROR:  query has no destination for result data
CONTEXT:  PL/pgSQL function ins_uuser(text,text,integer,text) line 3 at SQL statement

Upvotes: 2

Views: 286

Answers (1)

user330315
user330315

Reputation:

You don't need PL/pgSQL for this:

CREATE OR REPLACE FUNCTION ins_uuser(p_user_name text, p_real_name text, p_age int, p_city text)
RETURNS integer AS
$$
  INSERT INTO uuser(user_name, last_login, real_name, age, city)
  VALUES($1, CURRENT_TIMESTAMP, $2, $3, $4)
  RETURNING uid;
$$
LANGUAGE sql VOLATILE;

But if you really want to use PL/pgSQL (although that is usually slower) you need to do what the error message says: you need to store the result in some variable:

CREATE OR REPLACE FUNCTION ins_uuser(p_user_name text, p_real_name text, p_age int, p_city text)
RETURNS integer AS
$$
declare
  new_id integer;
BEGIN
  INSERT INTO uuser(user_name, last_login, real_name, age, city)
  VALUES($1, CURRENT_TIMESTAMP, $2, $3, $4)
  RETURNING uid
  into new_id; --<<< this stores the result in the variable

  return new_id;
END;
$$
LANGUAGE plpgsql VOLATILE;

Upvotes: 2

Related Questions