PostgreSQL Error 42601: How to Correctly Define Return Types for Functions Returning Records?

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

This error occurs when creating a function that returns a record without providing a column definition list. This article will explain the issue and provide a solution to resolve the error.

Issue

In PostgreSQL, functions that return records require a column definition list explicitly specifying the names and data types of the columns in the returned record. However, the code provided in the question attempts to return a record without defining the columns, resulting in the error "column definition list is required."

Solution

To resolve the error, you need to provide a column definition list for the function's return type. This can be done using the RETURNS keyword. For example, here's a corrected version of the function:

CREATE OR REPLACE FUNCTION get_user_by_username(
    username varchar(250),
    online boolean
) RETURNS TABLE (
    user_id int,
    user_name varchar(250),
    last_activity timestamptz
) AS $$
BEGIN
    IF online THEN
        UPDATE users
        SET last_activity = current_timestamp
        WHERE user_name = username;
    END IF;

    RETURN QUERY
    SELECT
        user_id,
        user_name,
        last_activity
    FROM
        users
    WHERE
        user_name = username
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

In this code, the RETURNS TABLE statement specifies the column names and data types of the record to be returned by the function.

Alternatively, you can avoid using a column definition list by returning a whole row or rows of an existing table. For example:

CREATE OR REPLACE FUNCTION get_user_by_username(
    username varchar(250),
    online boolean
) RETURNS SETOF users AS $$
BEGIN
    IF online THEN
        RETURN QUERY
        UPDATE users
        SET last_activity = current_timestamp
        WHERE user_name = username
        RETURNING *;
    ELSE
        RETURN QUERY
        SELECT *
        FROM
            users
        WHERE
            user_name = username;
    END IF;
END;
$$ LANGUAGE plpgsql;

In this case, the RETURNS SETOF users statement indicates that the function will return a set of rows of the users table, which already has the necessary column definitions.

Ensure that the column definitions match the actual table structure to avoid errors or unexpected behavior.

The above is the detailed content of PostgreSQL Error 42601: How to Correctly Define Return Types for Functions Returning Records?. For more information, please follow other related articles on the PHP Chinese website!