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

(Отказ от ответственности: PostgreSQL новичок.)

Хорошо, насколько я могу судить, моя функция правильно напоминает образцы, которые я видел. Может кто-нибудь подсказать мне, как мне заставить это работать?

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;
Источник

4 ответа

Решение

Если вы хотите создать функцию, возвращающую 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;
Источник

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

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;

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

Источник

Изменять

      SELECT * FROM foo(params);

к

      SELECT foo(params);

В вашем случае это будет

      SELECT get_user_by_username('myuser', true);
Источник

Поскольку вы возвращаете только одну запись (черезLIMIT 1), вы также можете удалитьSETOFи объявите свою функцию следующим образом:

      create or replace function x() returns record as $$
declare result record;
begin
  select
    1, 2, 3
  into result;
  return result;
end;
$$ language plpgsql;

А затем сослаться на функцию изSELECTоговорка вместоFROMпредложение (что по-прежнему невозможно, если вы не используете типизированную запись):

      select x();

Результат будет выглядеть так:

      |x      |
|-------|
|(1,2,3)|
Источник
Другие вопросы по тегам