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

Question

As far as I can tell, my function properly resembles the samples I've seen. Can someone clue me in as to how I get this to work?

create or replace function get_user_by_username(
    username varchar(250),
    online boolean
    ) returns setof record as $$
declare result record;
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,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment
    from
        users
    where
        user_name = username
    limit 1;

    return;
end;
$$ language plpgsql;

Answer 1

I created my_func() which returns SETOF RECORD type with a [RETURN NEXT][1] or [RETURN QUERY][1] statement as shown below:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
DECLARE 
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT row;
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY VALUES ('John','Smith'), ('David','Miller');
END;    
$$ LANGUAGE plpgsql;

Then, calling my_func() got the same error as shown below:

postgres=# SELECT * FROM my_func();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM my_func();
                      ^

So, I set a column definition list just after my_func() with AS, then I could call my_func() without error as shown below:

postgres=# SELECT * FROM my_func() AS (f_n TEXT, l_n TEXT);
  f_n  |  l_n
-------+--------
 John  | Smith
 David | Miller
(2 rows)

In addition, calling my_func() without a FROM clause got the syntax error as shown below:

postgres=# SELECT my_func() AS (f_n TEXT, l_n TEXT);
ERROR:  syntax error at or near "("
LINE 1: SELECT my_func() AS (f_n TEXT, l_n TEXT);

And, calling my_func() without a FROM clause and column definition list got [the error][2] as shown below:

postgres=# SELECT my_func();
ERROR:  materialize mode required, but it is not allowed in this context

Answer 2

Change

SELECT * FROM foo(params);

to

SELECT foo(params);

In your case it would be

SELECT get_user_by_username('myuser', true);

Answer 3

Return selected columns

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$;

Call:

SELECT * FROM get_user_by_username('myuser', true);

You had DECLARE result record; but didn't use the variable. I deleted the cruft.

You can return the record directly from the UPDATE, which is much faster than calling an additional SELECT statement. Use RETURN QUERY and UPDATE with a RETURNING clause.

If the user is not _online, default to a plain SELECT. This is also the (safe) default if the second parameter is omitted - which is only possible after providing that default with DEFAULT false in the function definition.

If you don't table-qualify column names (tablename.columnname) in queries inside the function, be wary of naming conflicts between column names and named parameters, which are visible (most) everywhere inside a function.
You can also avoid such conflicts by using positional references ($n) for parameters. Or use a prefix that you never use for column names: like an underscore (_username).

If users.username is defined unique in your table, then LIMIT 1 in the second query is just cruft. If it is not, then the UPDATE can update multiple rows, which is most likely wrong. I assume a unique username and trim the noise.

Define the return type of the function (like @ertx demonstrated) or you have to provide a column definition list with every function call, which is awkward.

Creating a type for that purpose (like @ertx proposed) is a valid approach, but probably overkill for a single function. That was the way to go in old versions of Postgres before we had RETURNS TABLE for that purpose - like demonstrated above.

You do not need a loop for this simple function.

Every function needs a language declaration. LANGUAGE plpgsql in this case.

I use timestamptz (timestamp with time zone) instead of timestamp (timestamp without time zone), which is the sane default. See:

• Ignoring time zones altogether in Rails and PostgreSQL

Return (set of) whole row(s)

To return all columns of the existing table users, there is a simpler way. Postgres automatically defines a composite type of the same name for every table. Just use RETURNS SETOF users to vastly simplify the query:

CREATE OR REPLACE FUNCTION get_user_by_username(_username text
                                              , _online bool DEFAULT false)
  RETURNS SETOF users
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp
      WHERE  u.user_name = _username
      RETURNING u.*;
   ELSE
      RETURN QUERY
      SELECT *
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

Return whole row plus custom addition

To address the question added by TheRealChx101 in a comment below:

What if you also have a calculated value in addition to a whole table? 😑

Not as simple, but doable. We can send the whole row type as one field, and add more:

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 "magic" is in the function call, where we (optionally) decompose the row type:

SELECT (users_row).*, custom_addition FROM get_user_by_username('foo', true);

db<>fiddle here (showing all)

---

If you need something more "dynamic", consider:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

Answer 4

if you would like to create function returning setof record, you'll need to define column types in your select statement

More info

Your query should look something like this:

select * from get_user_by_username('Username', True) as 
  f(user_id integer, user_name varchar, last_activity, varchar, created date, email        archar, approved boolean, last_lockout timestamp, last_login timestamp, 
  last_password_changed timestamp, password_question varchar, comment varchar)

(you will probably need to change the data types)

I personaly prefer the types approach. it assures that if the function is edited, all the queries will return correct results. It might be a pain because every time you modify function's arguments you'll need to recreate/drop types aswell tho.

Eg:

CREATE TYPE return_type as 
(user_id integer,
 user_name varchar,
 last_activity varchar,
 created timestamp,
 email varchar,
 approved boolean,
 last_lockout timestamp ,
 last_login timestamp,
 last_password_changed timestamp,
 password_question varchar,
 comment varchar);

create or replace function get_user_by_username( username varchar(250), online 

boolean) returns setof return_type as $$
declare _rec return_type;
begin
    if online then 
        update users
        set last_activity = current_timestamp
        where user_name = username;
    end if;
    for _rec in select
        user_id,
        user_name,
        last_activity,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment
      from
        users
      where
        user_name = username
      limit 1 
    loop

      return next _rec;

    end loop

end;
$$ language plpgsql;