PostgreSQL: ОШИБКА: 42601: необходим список определения столбцов для функций, возвращающих "запись",

Ответ 1

если вы хотите создать функцию, возвращающую setof record, вам нужно определить типы столбцов в вашем заявлении select

Подробнее

Ваш запрос должен выглядеть примерно так:

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)

(вам, вероятно, потребуется изменить типы данных)

Я лично предпочитаю подход типа. он гарантирует, что если функция будет отредактирована, все запросы вернут правильные результаты. Это может быть боль, потому что каждый раз, когда вы изменяете аргументы функции, вам нужно будет воссоздать/удалить типы, а также.

Например:

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;

Ответ 2

Возврат выбранных столбцов

CREATE OR REPLACE FUNCTION get_user_by_username(_username text, _online bool)
  RETURNS TABLE (
    user_id int
   ,user_name text
   ,last_activity timestamp
   , ... ) AS
$func$
BEGIN

IF _online THEN
   RETURN QUERY
   UPDATE users u 
   SET    last_activity = current_timestamp
   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$  LANGUAGE plpgsql;

Вызов:

SELECT * FROM get_user_by_username('myuser', TRUE)

Основные моменты

• У вас был DECLARE result record;, но не использовал переменную. Я удалил рывок.

• Вы можете вернуть запись непосредственно из UPDATE, которая намного быстрее, чем вызов дополнительного оператора SELECT. Используйте RETURN QUERY и UPDATE с предложением RETURNING.
  Если пользователь не является _online, по умолчанию используется обычный SELECT.

• Если в запросах внутри этой функции не назначены имена столбцов (tablename.columnname) в таблицах, будьте осторожны с конфликтами имен между именами столбцов и именованными параметрами, которые являются видимыми ( большинство) всюду внутри функции.
  Вы также можете избежать таких конфликтов, используя позиционные ссылки ($n) для параметров. Или используйте префикс, который вы никогда не используете для имен столбцов: например, подчеркивание (_username).

• Если users.username задано уникальный в вашей таблице, то LIMIT 1 во втором запросе просто круто.
  Если он не, то UPDATE может обновлять несколько строк, что наиболее вероятно неверно.
  Я предположил уникальный username и удалил треск.

• Определите возвращаемый тип функции (например, продемонстрированный @ertx), или вам нужно будет предоставить список определения столбца в каждом вызове функции, что неудобно.

• Создание типа для этой цели (например, предлагаемого @ertx) является допустимым подходом, но, вероятно, избыточным для одной функции. Это был способ пойти в старых версиях PostgreSQL, прежде чем мы RETURNS TABLE для этой цели - как показано выше.

• Для этой простой функции вам не нужен цикл.

• Каждой функции требуется декларация языка. LANGUAGE plpgsql в этом случае.

• Вероятно, нет смысла определять ограничение длины (varchar(250)) для параметра. Я упростил тип text.

Возвратить целую таблицу

Если вы хотите вернуть все столбцы таблицы users, существует более простой способ. PostgreSQL автоматически определяет тип составного типа с тем же именем для каждой таблицы. В этом случае вы можете использовать RETURNS SETOF users и значительно упростить запрос:

CREATE OR REPLACE FUNCTION get_user_by_username(_username text, _online bool)
  RETURNS SETOF users 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$  LANGUAGE plpgsql;

Если вам нужно что-то более "динамическое", подумайте:

• Рефакторинг функции PL/pgSQL для возврата вывода различных запросов SELECT