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

Доброго времени суток.
У меня проблемка — ищу уже второй день подряд, ответ на свой вопрос.
Мне нужно, чтобы функция отдала мне содержимое таблицы(в данном случае).
Нашел у вас на форуму замечательный ответ по сабжу http://postgresql.ru.net/node/178974#comment-875
В частности интересует второй изложенный способ. Сделал все как советовал автор ответа, но postgres выдает ошибку :
ERROR: function result type must be text because of OUT parameters
SQL state: 42P13
Вот листинг функции:

CREATE OR REPLACE FUNCTION test.fm (
	out o_name text) 
returns setof RECORD 
AS $$
 DECLARE
  rec RECORD;
BEGIN
	FOR rec IN EXECUTE 'select t.name from test.tb t'  
	LOOP
		RETURN next rec;
	END LOOP;
END;
$$ LANGUAGE plpgsql

Интуиция подсказывает, что в теле цикла должно быть, что-то типа:
o_name := r.name;

Пожалуйста подскажите, как правильно…
За ранее благодарен!

Hello

2008/7/28 Milan Oparnica <milan(dot)opa(at)gmail(dot)com>:
> Pavel Stehule wrote:
>>
>> Hello
>>
>> this is near my implemenation of stored procedures — it’s not far from
>> your view on prepared statements. There result of any unbound select
>> is directly forwarded to client — there is little bit different
>> syntax, but idea is same
>>
>>
>> create procedure foo(params)
>> as $$
>> begin
>> select a, b, c from tab1 — unbound select
>> select ….
>> end;
>>
>> and you can call with CALL statement. Curent implementation of SRF
>> function in plpgsql sn’t eficient for big results — it needs
>> materialised table in memory. Pipeline Oracle’s functions are better,
>> but we are limited one thread architecture.
>>
>> regards
>> Pavel Stehule
>>
>
> Yeah, this would be pretty the same.
>
> I just didn’t understand if this is already implemented in PG ?
>

no — I have workin prototype now — and I am searching sponsors for
finishing this work.

http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
http://okbob.blogspot.com/2007/11/first-real-procedures-on-postgresql.html

But it’s really similar to what do you want and it’s can be
interesting together with plpgpsm for you, because plpgpsm (SQL/PSM)
allows one statement procedures, like:

create procedure foo(…) as $$
select * from tab;
$$ language plpgpsm;

so this is standard syntax for persistent prepared statement

regards
Pavel Stehule

> I’ve tried to create a PROCEDURE in QueryTool of pgAdminIII and I get
> following result:
>
> ERROR: syntax error at or near «PROCEDURE»
> LINE 1: CREATE PROCEDURE aatestubnd(refcursor, insklid integer, inda…
> ^
>
> ********** Error **********
>
> ERROR: syntax error at or near «PROCEDURE»
> SQL state: 42601
> Character: 8
>
>
> If I try with CREATE FUNCTION I get this result (as expected):
>
> ERROR: function result type must be specified
>
> ********** Error **********
>
> ERROR: function result type must be specified
> SQL state: 42P13
>
>
> Is this because of QueryTool’s limitations or is this feature not yet
> implemented in Postgre ?
>
> Though, CREATE PROCEDURE should be a valid SQL 92 standard procedure.
>
> Best regards,
>
> Milan Oparnica
>
> —
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

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

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

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

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

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

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

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

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

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

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 тоже не нравится:

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

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

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

RAISE EXCEPTION 'ERR_MY_ERROR';

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

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

Это достаточно легко разобрать, но делать вещи таким образом — неправильно.

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

Что-то вроде:

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

// Редактировать 1 //

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

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

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

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.    
}

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

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

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

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

Чтение перед сном: https://www.postgresql.org/docs/current/static/errcodes-appendix.html

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

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

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

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

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

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

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

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

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

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 тоже не нравится:

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

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

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

RAISE EXCEPTION 'ERR_MY_ERROR';

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

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

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

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

Что-то вроде:

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

//Редактировать 1//

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

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

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

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.    
}

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

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

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

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

Чтение постельного белья: https://www.postgresql.org/docs/current/static/errcodes-appendix.html

I come from SQL Server world to PostgreSQL (9.0), and I am having a issue while migrating an stored procedure / function. The function is returning this error message:

SQLSTATE: 42601;
SQLERRM: query has no destination for result data

I need to return both, the query results and the two out params. The query results are represented as two columns named ‘paramName’ and ‘value’ with one row of data per select query.

What I need to do pass the values from columns selected by the query along with the OUT parameters in different resultsets like Transact-SQL does perfectly and avoid getting that error message?

This is the pl/pgsql function:

        CREATE OR REPLACE FUNCTION myplfunction(
            IN i_param1 character varying,
            IN i_param2 character varying,
            IN i_param3 character varying,
            IN i_param4 character varying,
            OUT o_call_status integer,
            OUT o_call_message character varying)
          RETURNS SETOF RECORD AS
        $BODY$
        DECLARE 
        val1 varchar;
        val2 varchar;
        val4 varchar;
       BEGIN
        -- A couple of IF THEN ommited here
        IF (v_solution_id IS NULL) THEN
            val1 := (Select column1 FROM tbl2 WHERE column2= i_param1);
            IF(val1 IS NULL) THEN
                o_call_status := 1005;
                o_call_message := column1 is not configured or invalid';
                RETURN;
            END IF;
        END IF;

            SELECT 'mycolumnname1' as paramName,mycolumn1 as value FROM tb1 
            WHERE column1 = val
            UNION ALL
            SELECT 'mycolumnname2' as paramName,mycolumn2 as value  FROM tb1 
            WHERE column1 = val
            UNION ALL
            SELECT 'mycolumnname3' as paramName,mycolumn3 as value  FROM tb2 
            WHERE column1 = val1 AND
            column4 = val4;      

            o_call_status := 0;
            o_call_message := '';
        RETURN;

            EXCEPTION WHEN OTHERS THEN 
                o_call_message := SQLERRM;
                o_call_status := SQLSTATE;
        end;

I am getting just the query result or the out params in the resultset. I am not able to figure out how to have both in the same function response.

UPDATE: Implemented with cursors as suggested by Erwin:

       CREATE OR REPLACE FUNCTION myplfunction(
            IN i_param1 character varying,
            IN i_param2 character varying,
            IN i_param3 character varying,
            IN i_param4 character varying,
            OUT o_call_status integer,
            OUT o_call_message character varying)
          RETURNS refcursor AS
        $BODY$
        DECLARE 
        val1 varchar;
        val2 varchar;
        query_cursor refcursor;
       BEGIN
        -- A couple of IF THEN ommited here
        IF (v_solution_id IS NULL) THEN
            val1 := (Select column1 FROM tbl2 WHERE column2= i_param1);
            IF(val1 IS NULL) THEN
                o_call_status := 1005;
                o_call_message := column1 is not configured or invalid';
                RETURN;
            END IF;
        END IF;

            open  query_cursor for SELECT 'mycolumnname1' as paramName,
                                           mycolumn1 as value FROM tb1 
            WHERE column1 = val
            UNION ALL
            SELECT 'mycolumnname2' as paramName,mycolumn2 as value  FROM tb1 
            WHERE column1 = val
            UNION ALL
            SELECT 'mycolumnname3' as paramName,mycolumn3 as value  FROM tb2 
            WHERE column1 = val1 AND
            column4 = val4;     

            o_call_status := 0;
            o_call_message := '';
            RETURN query_cursor;

            EXCEPTION WHEN OTHERS THEN 
                o_call_message := SQLERRM;
                o_call_status := SQLSTATE;
       end;

select * from  myplfunction(param1,param2,param3,param4);

But I get an error:

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

So this means I can not return a cursor when I have OUT params?

Also, does the return; in the IF THEN clause sentence terminates the function as intended?

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

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

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

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

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

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

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

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

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

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;

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

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

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

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

RAISE EXCEPTION 'ERR_MY_ERROR';

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

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

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

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

Что-то вроде:

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

// Редактировать 1 //

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

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

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

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.    
}

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

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

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

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

Чтение перед сном:
https://www.postgresql.org/docs/current/static/errcodes-appendix.html