sqldatabasepostgresqlplpgsqlset-returning-functions

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


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;

Solution

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