Error query has no destination for result data postgresql

В этом руководстве ты научишься использовать хранимые процедуры, чтобы дать имя твоим SELECT запросам и сделать их переиспользуемыми.

Когда ты работаешь с любой SQL базой данных, ты можешь заметить, что одни и те же SELECT запросы используются очень часто. Они бывают короткими, тогда их несложно писать с нуля.

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

SELECT запросы очень удобно сохранять как хранимые процедуры в PostgreSQL. Ты можешь дать им любое имя и вызывать когда тебе нужно.

Для примера, представим, что у нас есть таблица app_user, которая хранит всех пользователей нашего приложения.

Если тебе нужно получить количество пользователей твоего приложения, ты напишешь что-то вроде:

SELECT count(*) FROM app_user;

Чтобы дать имя этому SQL запросу, ты можешь сохранить его как stored procedure.

create or replace function count_app_users()
    returns table
            (
                total bigint
            )
    language plpgsql
as
$$
begin
    return query (
      select count(*) from app_user
    );
end;
$$;

Давай разберем пример по шагам:

• В первой строке мы создаем функцию и называем ее count_app_users.
• Команда create or replace используется для того, чтобы создать функцию, если она не существует, или обновить ее, если она уже есть. Если ты уверен в том, что функции еще нет, можешь использовать обычный create
• Дальше мы явно указываем тип результата. Она равен table для всех SELECT запросов.
• В скобках мы указываем столбцы итоговой таблицы и их тип.
• Команду language plpgsql и несколько следующих строк ты можешь рассматривать как шаблон и не слишком вникать в них
• Сам запрос мы помещаем внутрь выражения return query(...) которое обернуто в $$ begin и end $$

Вызываем хранимую процедуру

После того, как мы сохранили SELECT запрос, мы можем вызвать его.

SELECT * FROM count_app_users();

Такой синтаксис получается потому, что процедура возвращает таблицу. Чтобы получить все ее строки мы выполняем обычный SELECT *

Передаем аргументы в хранимую процедуру PostgreSQL

Дальше, представим, что мы хотим посчитать только пользователей созданных после определенной даты.

Чтобы сделать это, нам нужно передать параметра внутрь хранимой процедуры и использовать его внутри SELECT запроса. В сигнатуру процедуры, мы добавим имя параметров и их типы. Потом, внутри SELECT, мы обратимся к ним.

create or replace function count_app_users(after timestamp) -- declaring the "after" parameter
    returns table
            (
                total bigint
            )
    language plpgsql
as
$$
begin
    return query (
      select count(*) from app_user
      where created_at > after                              -- using the "after" parameter
    );
end;
$$;

Теперь мы можем передать дату в процедуру count_app_users и получить количество всех пользователей созданных после этой даты.

SELECT * FROM count_app_users('2020-11-01');

Удаление хранимой процедуры PostgreSQL

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

И у нас получилось две функции. Первая — count_app_users, вторая — count_app_users(after). Обе они отлично живут в SQL базе данных.

Если одна из них тебе не нужна, то ее можно удалить так:

DROP function count_app_users();

Синтаксис похож на удаление таблицы и в нем нет каких-то подводных камней.

Посмотреть текст хранимой процедуры PostgreSQL

Наш пример с count намеренно максимально упрощен, но если у тебя более длинная функция, то бывает полезно получить ее текст. Например, чтобы понять, как она работает или чтобы дальше его изменить.

Чтобы получить текст хранимой процедуры в PostgreSQL, тебе нужно знать ее имя:

SELECT prosrc
FROM pg_proc
WHERE proname = 'count_app_users';

Такой запрос вернет нам все, что мы писали между begin и end при создании.

Разбираемся с частыми ошибками

Первая ошибка с которой ты можешь столкнуться при сохранении SELECT запроса выглядит примерно так:

ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Where: PL/pgSQL function count_app_users() line 3 at SQL statement

Она возникает если ты не оберешь SELECT запрос в выражение return query(...).

Дальше, может быть ошибка связанная с неправильным вызовом хранимой процедуры в PostgreSQL:

ERROR: cannot use RETURN QUERY in a non-SETOF function

Скорее всего ты попробовал вызывать функцию так:

Или так:

SELECT count_app_users();

Так как наша функция возвращала таблицу, то и результат из этой таблицы нужно получать как из любых других. С помощью SELECT.

SELECT * FROM count_app_users();

Заключение

Сохранять SELECT запросы в хранимые процедуры PostgreSQL очень удобно. Потом их можно вызывать по имени и не нужно будет каждый раз писать запрос с нуля.

Мы разобрали очень простой пример, но ты можешь сохранять SELECT запросы любой сложности.

Используй джойны, агрегацию, фильтр, сортировку и больше не пиши одни и те же запросы раз за разом.

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;