Error ошибка необходимо указать тип результата функции sql state 42p13

Проблема при возврате результата в функцию PostgreSQL

Я пришел из мира SQL Server в PostgreSQL (9.0), и у меня возникает проблема при переносе хранимой процедуры/функции. Функция возвращает это сообщение об ошибке:

Мне нужно вернуть оба, результаты запроса и два параметра. Результаты запроса представляются в виде двух столбцов с именем “paramName” и “value” с одной строкой данных для каждого запроса выбора.

Что мне нужно сделать, чтобы передать значения из столбцов, выбранных по запросу, вместе с параметрами OUT в разных наборах результатов, например Transact-SQL, и избежать получения этого сообщения об ошибке?

Это функция pl/pgsql:

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

ОБНОВЛЕНИЕ: Выполнено с помощью курсоров, предложенных Эрвином:

Но я получаю сообщение об ошибке:

Значит, я не могу вернуть курсор, когда у меня есть параметры OUT?

Кроме того, происходит return; в предложении IF THEN прекращается функция, как предполагалось?

У вашего кода есть ряд ошибок. Это должно работать:

Решение основной проблемы: используйте RETURN QUERY чтобы на самом деле вернуть результат. SELECT без цели вызвал сообщение об ошибке.

Несколько небрежных ошибок: отсутствующая одинарная кавычка, внештатные запятые, отсутствует END IF; , несколько WHERE …

Вам нужно ‘mycolumnname1’::text строковый литерал в соответствии с объявленным типом вывода: ‘mycolumnname1’::text

Форма val1 := column1 FROM tbl2 WHERE. ; возможно, но обескуражен. Лучше не смешивать plpgsql и SQL-код таким образом. Используйте альтернативу, предоставленную мной (только для одного параметра) или SELECT INTO .

Я бы посоветовал не использовать идентификаторы CAMeL, даже если это разрешено. Если двойные кавычки не преобразуются в нижний регистр.

Источник

Postgres: возврат результатов или ошибки из сохраненных функций

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

Рассмотрим следующий пример надуманного псевдокода:

Функция может завершиться неудачно с определенной ошибкой или завершиться успешно и вернуть 0 или более ресурсов.

Сначала я попытался создать настраиваемый тип, который всегда будет использоваться в качестве стандартного возвращаемого типа в каждой функции:

Однако Postgres не позволяет этого:

Затем я обнаружил параметры OUT и попытался использовать следующее:

Постгресу это тоже не нравится:

Также попробовал аналогичную функцию, но в обратном порядке: возврат настраиваемого объекта app.appresult и установка параметра OUT на «SETOF RECORD». Это тоже было недопустимо.

Наконец, я изучил обработку исключений Postgres, используя

Итак, в примере функции я просто подниму эту ошибку и вернусь. Это привело к тому, что драйвер отправил ошибку как:

Это достаточно просто для синтаксического анализа, но делать что-то таким образом кажется неправильным.

Как лучше всего решить эту проблему? Возможно ли иметь собственный объект AppResult, который я мог бы вернуть?

Думаю, я больше склоняюсь к решению @Laurenz Albe.

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

Использование RAISE, похоже, позволяет добиться этого, а драйвер C++ позволяет легко проверять состояние ошибки, возвращаемое из запроса.

Мне также интересно использовать пользовательские коды SQLSTATE вместо анализа строки драйвера.

Выброс «__404» может означать, что в ходе выполнения моих служебных программ он не может продолжаться, потому что не была найдена необходимая запись.

При вызове функции sql из моего приложения у меня есть общее представление о том, что означает сбой с ‘__404’ и как с этим справиться. Это позволяет избежать дополнительного этапа анализа строки ошибки драйвера.

Я также вижу потенциал того, что это плохая идея.

Вызов исключения в случае ошибки является правильное решение

Это немного основано на мнении, но я думаю, что выдача ошибки — лучшее и наиболее элегантное решение. Вот для чего нужны ошибки!

Чтобы различать различные сообщения об ошибках, вы можете использовать SQLSTATE, которые начинаются с 6, 8 или 9 (они не используются), тогда вам не нужно зависеть от формулировки сообщения об ошибке.

Вы можете вызвать такую ​​ошибку с помощью

Мы делаем что-то похожее на то, что вы пытаетесь сделать, но мы используем TEXT , а не ANYELEMENT , потому что (почти?) Любой тип может быть преобразован в TEXT и обратно. Итак, наш тип выглядит примерно так:

(errors our_error_type[], result TEXT)

Функция, которая возвращает это, сохраняет ошибки в массиве errors (это просто какой-то настраиваемый тип ошибки) и может сохранять результат (преобразованный в текст) в поле result .

Вызывающая функция знает, какой тип она ожидает, поэтому она может сначала проверить массив errors , чтобы увидеть, были ли возвращены какие-либо ошибки, а если нет, она может привести значение result к ожидаемому типу возврата.

В качестве общего наблюдения я считаю, что исключения более элегантны (возможно, потому, что я исхожу из опыта работы с C#). Единственная проблема заключается в том, что обработка исключений plpgsql (относительно) медленная, поэтому это зависит от контекста — если вы запускаете что-то много раз в цикле, я бы предпочел решение, которое не использует обработку исключений; если это одиночный вызов и / или особенно когда вы хотите его прервать, я предпочитаю создавать исключение. На практике мы используем и то, и другое на разных этапах стека вызовов.

И, как отметила Лоренц Альбе, вы не предназначены для «синтаксического анализа» исключений, а скорее для создания исключения с определенными значениями в определенных полях, которые функция, перехватывающая исключение, может затем извлекать и действовать напрямую.

В качестве примера:

Я воссоздал настраиваемый тип возвращаемого значения как и попытался выбрать строки из таблицы в поле результата: gist.github.com/Think7/7d30047c73e4c41d78b293a85127dee2 Результат: «(f, MY_ERROR, Wendy)», возвращенный драйвером в виде строки . Мне нужен весь набор результатов, возвращенный в приложение.

@Andrew Если вы хотите, чтобы он возвращал одну строку, выполните: SELECT resources::TEXT FROM app.resources INTO vReturn.result; . Это выбирает всю запись в нераскрытом состоянии (не технический термин). Затем вызывающий код может вернуть его и при желании расширить для доступа к отдельным полям. Однако, если вы хотите вернуть несколько строк, это не сработает (если вы не объедините их, но тогда это станет глупо), и вам лучше использовать исключения.

@Andrew См. Отредактированный пример того, как это работает.

Источник

Re: Function Syntax Help

Subject: Re: Function Syntax Help Date: 2009-10-30 19:50:42 Message-ID: BD69807DAE0CE44CA00A8338D0FDD083534BA949@oma00cexmbx03.corp.westworlds.com Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-sql

Thanks, I changed my code to this, it compiled, and it seems to be running now:

CREATE OR REPLACE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) RETURNS VOID AS $$
DECLARE
mycount integer;
newstart timestamp := mystart;
newstop timestamp := mystop;
BEGIN
WHILE newstart newstop;
INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES (newstart,mycount);
newstart := newstart + INTERVAL ‘1 minute’;
newstop := newstop + INTERVAL ‘1 minute’;
END LOOP;
END;
$$ LANGUAGE ‘plpgsql’ VOLATILE;

From: epailty(at)googlemail(dot)com [mailto:epailty(at)googlemail(dot)com] On Behalf Of Brian Modra
Sent: Friday, October 30, 2009 2:46 PM
To: Plugge, Joe R.
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. >
Thanks Brian, I changed it to this:

CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) RETURNS VOID AS $$
DECLARE
mycount integer;
BEGIN
WHILE mystart mystop;
INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES (mystart,mycount);
mystart := mystart + INTERVAL ‘1 minute’;
mystop := mystop + INTERVAL ‘1 minute’;
END LOOP;
END;
$$ LANGUAGE ‘plpgsql’ STABLE;

But now am getting a different error:

]$ cat gen_simultaneous_calls.sql | psql holly
ERROR: «$1» is declared CONSTANT
CONTEXT: compilation of PL/pgSQL function «gen_simultaneous_calls» near line 7

mystart and mystop are constants.

you could declare variables and copy those into them, and the modify the new variables.

From: epailty(at)googlemail(dot)com [mailto:epailty(at)googlemail(dot)com ] On Behalf Of Brian Modra
Sent: Friday, October 30, 2009 2:29 PM
To: Plugge, Joe R.
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. >
I am trying to create a function that will grind through a cdr table and populate another table. I am trying to load the function and am getting the following error:

ERROR: function result type must be specified

CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) AS $$ DECLARE

you need to tell it the return type. If there is none, «returns void»

e.g.
CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) returns void AS $$

WHILE mystart mystop;

INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES (mystart,mycount);

mystart := mystart + INTERVAL ‘1 minute’;

mystop := mystop + INTERVAL ‘1 minute’;

$$ LANGUAGE ‘plpgsql’ STABLE;

Joe R. Plugge
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0349 | Cell 402-517-2710 | jrplugge(at)west(dot)com

This electronic message transmission, including any attachments, contains information from West Corporation which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited.

If you have received this electronic transmission in error, please notify the sender immediately by a «reply to sender only» message and destroy all electronic and hard copies of the communication, including attachments.

Источник

return query and function result type must be real because of OUT parameters

I was doing some test to track down an error in a more
complicated function and I stumbled in this behaviour I can’t
understand:

create or replace function test.test(out _rank float4) returns setof
record as $$
begin
return query select 1::float4;
return;
end;
$$ language plpgsql stable;

ERROR: function result type must be real because of OUT parameters

ERROR: function result type must be real because of OUT parameters
SQL state: 42P13

same with text etc.

Responses

• Re: return query and function result type must be real because of OUT parameters at 2008-12-24 14:19:22 from Pavel Stehule

Browse pgsql-general by date

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

Postgres: возврат результатов или ошибка из хранимых функций

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

Рассмотрим следующий надуманный пример psudeocode:

Функция может выйти из строя с определенной ошибкой или преуспеть и вернуть 0 или более ресурсов.

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

Postgres этого не позволяет:

Затем я обнаружил параметры OUT и попытался выполнить следующие действия:

Postgres тоже не нравится:

Также попробовали аналогичную функцию, но изменили ее на противоположную сторону: возврат пользовательского объекта app.appresult и установка параметра OUT в «SETOF RECORD». Это также не допускалось.

Наконец, я рассмотрел обработку исключений Postgres, используя

Таким образом, в функции примера я просто подниму эту ошибку и вернусь. Это привело к тому, что драйвер отправил ошибку:

Это достаточно просто, чтобы разобрать, но делать то, что чувствует себя неправильно.

Каков наилучший способ решить эту проблему? Возможно ли иметь собственный объект AppResult, который я мог бы вернуть?

Я думаю, что я больше склоняюсь к решению @Laurenz Albe.

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

Использование RAISE, похоже, достигает этого, а драйвер C++ позволяет легко проверять условие ошибки, возвращаемое из запроса.

Я также интересуюсь использованием пользовательских кодов SQLSTATE вместо разбора строки драйвера.

Бросание «__404» может означать, что во время выполнения моего SPs оно не могло продолжаться, потому что некоторая запись не была найдена.

Когда вы вызываете функцию sql из моего приложения, у меня есть общее представление о том, что она не с помощью «__404» означает и как ее обрабатывать. Это позволяет избежать дополнительного этапа разбора строки ошибки драйвера.

Я также вижу потенциал этого плохой идеи.

Это немного основано на мнениях, но я думаю, что бросание ошибки — лучшее и самое изящное решение. Для этого нужны ошибки!

Чтобы различать различные сообщения об ошибках, вы можете использовать SQLSTATE, которые начинаются с 6, 8 или 9 (они не используются), тогда вам не нужно зависеть от формулировки сообщения об ошибке.

Вы можете поднять такую ошибку с помощью

Мы делаем что-то похожее на то, что вы пытаетесь сделать, но мы используем TEXT а не ANYELEMENT , потому что (почти?) Любой тип может быть ANYELEMENT в TEXT и обратно. Поэтому наш тип выглядит примерно так:

(errors our_error_type[], result TEXT)

Функция, которая возвращает это, хранит ошибки в массиве errors (это всего лишь некоторый произвольный тип ошибки) и может сохранять результат (преобразование в текст) в поле result .

Вызывающая функция знает, какой тип она ожидает, поэтому она может сначала проверить массив errors чтобы увидеть, были ли возвращены какие-либо ошибки, а если нет, то можно применить значение result к ожидаемому типу возврата.

Как общее замечание, я думаю, что исключения более элегантны (возможно, потому, что я исхожу из aС# фона). Единственная проблема заключается в том, что обработка исключений plpgsql (относительно) медленная, поэтому она зависит от контекста — если вы выполняете что-то много раз в цикле, я бы предпочел решение, которое не использует обработку исключений; если это один вызов и/или особенно, когда вы хотите его прервать, я предпочитаю создавать исключение. На практике мы используем оба в разных точках в наших столах вызовов.

И, как отметил Лоренц Албе, вы не должны «разбирать» исключения, так же как возбуждать исключение с определенными значениями в определенных полях, которые функция, которая ловит исключение, может извлекать и действовать напрямую.

Источник

I am struggling to figure out how to best handle the return of results or errors to my application from Postgres stored functions.

Consider the following contrived psudeocode example:

app.get_resource(_username text)
    RETURNS <???>

BEGIN

    IF ([ ..user exists.. ] = FALSE) THEN
        RETURN 'ERR_USER_NOT_FOUND';
    END IF;

    IF ([ ..user has permission.. ] = FALSE) THEN
        RETURN 'ERR_NO_PERMISSION';
    END IF;

    -- Return the full user object.
    RETURN QUERY( SELECT 1 
        FROM app.resources
        WHERE app.resources.owner = _username);

END

The function can fail with a specific error or succeed and return 0 or more resources.

At first I tried creating a custom type to always use as a standard return type in eachh function:

CREATE TYPE app.appresult AS (
  success boolean,
  error   text,
  result  anyelement
);

Postgres does not allow this however:

[42P16] ERROR: column "result" has pseudo-type anyelement

I then discovered OUT parameters and attempted the following uses:

CREATE OR REPLACE FUNCTION app.get_resource(
    IN      _username   text,
    OUT     _result app.appresult -- Custom type 
                                  -- {success bool, error text}
)

RETURNS SETOF record
AS
$$
BEGIN

  IF 1 = 1 THEN -- just a test
    _result.success = false;
    _result.error   = 'ERROR_ERROR';
    RETURN NULL;
  END IF;

  RETURN QUERY(SELECT * FROM app.resources);

END;
$$
  LANGUAGE 'plpgsql' VOLATILE;

Postgres doesn’t like this either:

[42P13] ERROR: function result type must be app.appresult because of OUT parameters

Also tried a similar function but reversed: Returning a custom app.appresult object and setting the OUT param to «SETOF RECORD». This was also not allowed.

Lastly i looked into Postgres exception handling using

RAISE EXCEPTION 'ERR_MY_ERROR';

So in the example function, i’d just raise this error and return.
This resulted in the driver sending back the error as:

"ERROR:  ERR_MY_ERRORnCONTEXT:  PL/pgSQL function app.test(text) line 6 at RAISEn(P0001)"

This is easy enough to parse but doing things this way feels wrong.

What is the best way to solve this problem?
Is it possible to have a custom AppResult object that i could return?

Something like:

{ success bool, error text, result <whatever type> }

//Edit 1 //

I think I’m leaning more towards @Laurenz Albe solution.

My main goal is simple: Call a stored procedure which can return either an error or some data.

Using RAISE seems to accomplish this and the C++ driver allows easy checking for an error condition returned from a query.

if ([error code returned from the query] == 90100)
{
    // 1. Parse out my overly verbose error from the raw driver
    //    error string.
    // 2. Handle the error.    
}

I’m also wondering about using custom SQLSTATE codes instead of parsing the driver string.

Throwing ‘__404’ might mean that during the course of my SPs execution, it could not continue because some record needed was not found.

When calling the sql function from my app, i have a general idea of what it failing with a ‘__404’ would mean and how to handle it. This avoids the additional step of parsing driver error string.

I can also see the potential of this being a bad idea.

Bedtime reading:
https://www.postgresql.org/docs/current/static/errcodes-appendix.html

I am struggling to figure out how to best handle the return of results or errors to my application from Postgres stored functions.

Consider the following contrived psudeocode example:

app.get_resource(_username text)
    RETURNS <???>

BEGIN

    IF ([ ..user exists.. ] = FALSE) THEN
        RETURN 'ERR_USER_NOT_FOUND';
    END IF;

    IF ([ ..user has permission.. ] = FALSE) THEN
        RETURN 'ERR_NO_PERMISSION';
    END IF;

    -- Return the full user object.
    RETURN QUERY( SELECT 1 
        FROM app.resources
        WHERE app.resources.owner = _username);

END

The function can fail with a specific error or succeed and return 0 or more resources.

At first I tried creating a custom type to always use as a standard return type in eachh function:

CREATE TYPE app.appresult AS (
  success boolean,
  error   text,
  result  anyelement
);

Postgres does not allow this however:

[42P16] ERROR: column "result" has pseudo-type anyelement

I then discovered OUT parameters and attempted the following uses:

CREATE OR REPLACE FUNCTION app.get_resource(
    IN      _username   text,
    OUT     _result app.appresult -- Custom type 
                                  -- {success bool, error text}
)

RETURNS SETOF record
AS
$$
BEGIN

  IF 1 = 1 THEN -- just a test
    _result.success = false;
    _result.error   = 'ERROR_ERROR';
    RETURN NULL;
  END IF;

  RETURN QUERY(SELECT * FROM app.resources);

END;
$$
  LANGUAGE 'plpgsql' VOLATILE;

Postgres doesn’t like this either:

[42P13] ERROR: function result type must be app.appresult because of OUT parameters

Also tried a similar function but reversed: Returning a custom app.appresult object and setting the OUT param to «SETOF RECORD». This was also not allowed.

Lastly i looked into Postgres exception handling using

RAISE EXCEPTION 'ERR_MY_ERROR';

So in the example function, i’d just raise this error and return.
This resulted in the driver sending back the error as:

"ERROR:  ERR_MY_ERRORnCONTEXT:  PL/pgSQL function app.test(text) line 6 at RAISEn(P0001)"

This is easy enough to parse but doing things this way feels wrong.

What is the best way to solve this problem?
Is it possible to have a custom AppResult object that i could return?

Something like:

{ success bool, error text, result <whatever type> }

//Edit 1 //

I think I’m leaning more towards @Laurenz Albe solution.

My main goal is simple: Call a stored procedure which can return either an error or some data.

Using RAISE seems to accomplish this and the C++ driver allows easy checking for an error condition returned from a query.

if ([error code returned from the query] == 90100)
{
    // 1. Parse out my overly verbose error from the raw driver
    //    error string.
    // 2. Handle the error.    
}

I’m also wondering about using custom SQLSTATE codes instead of parsing the driver string.

Throwing ‘__404’ might mean that during the course of my SPs execution, it could not continue because some record needed was not found.

When calling the sql function from my app, i have a general idea of what it failing with a ‘__404’ would mean and how to handle it. This avoids the additional step of parsing driver error string.

I can also see the potential of this being a bad idea.

Bedtime reading:
https://www.postgresql.org/docs/current/static/errcodes-appendix.html