Вопрос:
Я пришел из мира SQL Server в PostgreSQL (9.0), и у меня возникает проблема при переносе хранимой процедуры/функции. Функция возвращает это сообщение об ошибке:
SQLSTATE: 42601; SQLERRM: query has no destination for result data
Мне нужно вернуть оба, результаты запроса и два параметра. Результаты запроса представляются в виде двух столбцов с именем “paramName” и “value” с одной строкой данных для каждого запроса выбора.
Что мне нужно сделать, чтобы передать значения из столбцов, выбранных по запросу, вместе с параметрами OUT в разных наборах результатов, например Transact-SQL, и избежать получения этого сообщения об ошибке?
Это функция pl/pgsql:
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;
Я получаю только результат запроса или параметры out в наборе результатов. Я не могу понять, как использовать оба метода в одном и том же функциональном ответе.
ОБНОВЛЕНИЕ: Выполнено с помощью курсоров, предложенных Эрвином:
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);
Но я получаю сообщение об ошибке:
ERROR: function result type must be record because of OUT parameters SQL state: 42P13
Значит, я не могу вернуть курсор, когда у меня есть параметры OUT?
Кроме того, происходит return; в предложении IF THEN прекращается функция, как предполагалось?
Ответ №1
У вашего кода есть ряд ошибок. Это должно работать:
CREATE OR REPLACE FUNCTION myplfunction(i_param1 text, i_param2 text , i_param3 text, i_param4 text) RETURNS TABLE(param_name text, param_value text) AS $func$ DECLARE val1 text; val2 text; o_call_status integer; o_call_message text; — without purpose BEGIN 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’; — I suggest this alternative: RAISE EXCEPTION ‘column1 is not configured or invalid’; END IF; END IF; RETURN QUERY SELECT ‘mycolumnname1’::text, mycolumn1 FROM tb1 WHERE column1 = val — val? UNION ALL SELECT ‘mycolumnname2’, mycolumn2 FROM tb1 WHERE column1 = val — val? UNION ALL SELECT ‘mycolumnname3’, mycolumn2 FROM tb2 WHERE column2 = val2 AND tb2paramName4 = i_val3; o_call_status := 0; o_call_message := »; EXCEPTION WHEN OTHERS THEN o_call_message := SQLERRM; o_call_status := SQLSTATE; — without purpose END $func$ LANGUAGE plpgsql;
-
Решение основной проблемы: используйте RETURN QUERY чтобы на самом деле вернуть результат. SELECT без цели вызвал сообщение об ошибке.
-
Несколько небрежных ошибок: отсутствующая одинарная кавычка, внештатные запятые, отсутствует END IF; , несколько WHERE…
-
Вам нужно ‘mycolumnname1’::text строковый литерал в соответствии с объявленным типом вывода: ‘mycolumnname1’::text
-
Форма val1 := column1 FROM tbl2 WHERE…; возможно, но обескуражен. Лучше не смешивать plpgsql и SQL-код таким образом. Используйте альтернативу, предоставленную мной (только для одного параметра) или SELECT INTO.
-
Я бы посоветовал не использовать идентификаторы CAMeL, даже если это разрешено. Если двойные кавычки не преобразуются в нижний регистр.
