How to Fix PostgreSQL Error 42601: 'a column definition list is required for functions returning 'record''?

PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

The error "a column definition list is required for functions returning "record"" indicates that a function defined to return a record data type is missing the column names and data types in its return statement. To resolve this error, specify the column definitions in the RETURNING clause or use RETURNS SETOF .

As demonstrated in the following code, you can define the column names and data types in the RETURNING clause:

CREATE OR REPLACE FUNCTION get_user_by_username(_username text
                                              , _online bool DEFAULT false)
  RETURNS TABLE (
    user_id int
  , user_name varchar
  , last_activity timestamptz
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp  -- ts with time zone
      WHERE  u.user_name = _username
      RETURNING u.user_id
              , u.user_name
              , u.last_activity;
   ELSE
      RETURN QUERY
      SELECT u.user_id
           , u.user_name
           , u.last_activity
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

Alternatively, you can use RETURNS SETOF to return a set of records, where is a composite type that represents the structure of the returned data. For example:

CREATE OR REPLACE FUNCTION get_user_by_username3(_username text
                                               , _online bool DEFAULT false)
  RETURNS TABLE (
    users_row users
  , custom_addition text
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp  -- ts with time zone
      WHERE  u.user_name = _username
      RETURNING u  -- whole row
              , u.user_name || u.user_id;
   ELSE
      RETURN QUERY
      SELECT u, u.user_name || u.user_id
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

The above is the detailed content of How to Fix PostgreSQL Error 42601: 'a column definition list is required for functions returning 'record''?. For more information, please follow other related articles on the PHP Chinese website!