Sql error 42p13 error function result type must be specified

Проблема при возврате результата в функцию 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 (относительно) медленная, поэтому она зависит от контекста — если вы выполняете что-то много раз в цикле, я бы предпочел решение, которое не использует обработку исключений; если это один вызов и/или особенно, когда вы хотите его прервать, я предпочитаю создавать исключение. На практике мы используем оба в разных точках в наших столах вызовов.

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

Источник

When the PostgreSQL function has one OUT parameter, then result type should be same type. When there are more OUT parameters, then result type must be RECORD. Translation looks little bit random (when the Oracle procedures are translated).

CREATE OR REPLACE FUNCTION get_exists_spoj_cislo ( mID_CCLE CLEOSOBY.ID_CCLE%TYPE , mSpoj_cislo OUT NAJSIPOCIS.SPOJOVE_CISLO%TYPE , mSpojCislo_New NAJSIPOCIS.SPOJOVE_CISLO%TYPE , mID_APROVOZNA NAJSIPOCIS.ID_APROVOZNA%TYPE ) RETURNS VOID AS $body$                                                                                                        
DECLARE                                                                                                                                                                                                                                                                                                                                                       
  -- Change this to reflect the dblink connection string                                                                                                                                                                                                                                                                                                
  v_conn_str  text := 'port=5432 dbname=testdb host=localhost user=pguser password=pgpwd';                                                                                                                                                                                                                                                              
  v_query     text;                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                              
BEGIN                                                                                                                                                                                                                                                                                                                                                         
 v_query := 'SELECT true FROM get_exists_spoj_cislo_atx ( ' || quote_nullable(mID_CCLE) || ',' || quote_nullable(mSpoj_cislo) || ',' || quote_nullable(mSpojCislo_New) || ',' || quote_nullable(mID_APROVOZNA) || ' )';                                                                                                                                
 PERFORM * FROM dblink(v_conn_str, v_query) AS p (ret boolean);                                                                                                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                                                                                                              
END;                                                                                                                                                                                                                                                                                                                                                          
$body$ LANGUAGE plpgsql SECURITY DEFINER; 

There can be used simple trick. When any OUT parameter is defined, then RETURNS clause should not be used. PostgreSQL choose correct type by self. So, when there is a originally procedure, and when there are any OUT parameter, then don’t use RETURNS. elsewhere use RETURNS void