Очистка таблицы возвращаемой из функции / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Очистка таблицы возвращаемой из функции

7 сообщений из 7, страница 1 из 1

Очистка таблицы возвращаемой из функции

#39511066

NumberOne

Гость

Здравствуйте, необходим совет для решения следующей проблемы:

Выполняется некая функция. (Пример снизу отношения к этой функции не имеет, но вполне приемлем для демонстрации проблемы) В ходе своей работы она выполняет некоторые действия (копирование или перемещение сущностей) и после каждого такого действия вставляет с помощью конструкций RETURN NEXT и RETURN QUERY строки в возвращаемую таблицу. В нескольких местах этой функции вызывается другая функция (будем называть ее "внутренней"), которая может вернуть значение, обозначающее ошибку. Если внутренняя функция вернула ошибку, то надо откатить все изменения внесенные внешней функцией и вернуть из нее таблицу всего с одной строкой, обозначающей ошибку.

Откатываю изменения я, с помощью RAISE EXCEPTION, затем, чтобы вернуть строку с ошибкой, перехватываю исключение EXCEPTION WHEN SQLSTATE и добавляю в возвращаемую таблицу строку об ошибке.

Проблема в том, что RAISE EXCEPTION отменяет изменения только в таблицах, а возвращаемая таблица забивается строками, которые добавлялись в процессе работы функции.

Как это можно исправить? (Т.е., как вернуть только одну строку об ошибке из блока перехвата исключений?)

Результат работы приведенного ниже примера в прикрепленном изображении.

Дополнительный вопрос, правильно я понимаю, что RAISE EXCEPTION всегда отменяет все действия произведенные функцией, в независимости от того перехватываем мы исключения или нет?

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.

CREATE TEMP TABLE test (old_id uuid, new_id uuid);

CREATE OR REPLACE FUNCTION tst(id1 uuid)
RETURNS TABLE (obj_id uuid, test_id uuid) AS $$
DECLARE arr uuid[];
DECLARE arr2 uuid[];
DECLARE res_string RECORD;
BEGIN
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000005';
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000006';
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000007';
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000008';
    FOR res_string IN SELECT * FROM test LOOP
				-- Устанавливаем для строки возвращаемой таблицы значения из запроса
				obj_id = res_string.old_id;
				IF (obj_id = '00000000-0000-0000-0000-000000000004') THEN
				BEGIN
					RAISE EXCEPTION USING errcode='IRCER', message='Ошибка', hint='Вызов функции вернул ошибку';
				END;
				END IF;
				test_id = res_string.new_id;
				RETURN NEXT;
			END LOOP;
    SELECT ARRAY(SELECT old_id FROM test) INTO arr;
    FOR i IN 1..ARRAY_LENGTH(arr, 1) LOOP
    	arr2[i] = uuid_generate_v1();
    END LOOP;
    arr2[3] = NULL;
    arr = (SELECT ARRAY_CAT(arr, (SELECT ARRAY(SELECT old_id FROM test WHERE old_id = ANY (arr)))));
    FOR i IN (ARRAY_LENGTH(arr2, 1)+1)..ARRAY_LENGTH(arr, 1) LOOP
    	arr2[i] = uuid_generate_v1();
    END LOOP;
    RETURN QUERY SELECT t.a, t.b FROM (SELECT unnest(arr) AS a, unnest(arr2) AS b) AS t;
	RETURN;
    EXCEPTION WHEN SQLSTATE 'IRCER' THEN
	BEGIN
        -- Установка кода "Копирование невозможно"
        obj_id = '00000000-0000-0000-0000-000000000007';
        test_id = '00000000-0000-0000-0000-000000000007';
        RETURN NEXT;
    	RETURN;
    END;
    
END; $$ LANGUAGE plpgsql;

SELECT tst('00000000-0000-0000-0000-000000000005')

...

Рейтинг:

0 / 0

27.08.2017, 00:03

| Ответить | Цитировать | Написать

Очистка таблицы возвращаемой из функции

#39511110

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

NumberOne,

упасть в первой, отменив её возврат (оно даже не пайп, а накопитель)
и вывести нужное -- во второй.
примерно так (удаляю все ненужное)

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

drop TABLE IF EXISTS test ;
CREATE TEMP TABLE  test (old_id uuid, new_id uuid);

CREATE OR REPLACE FUNCTION tst(id1 uuid)
RETURNS TABLE (obj_id uuid, test_id uuid) AS $$
DECLARE arr uuid[];
DECLARE arr2 uuid[];
DECLARE res_string RECORD;
BEGIN

	INSERT INTO test SELECT '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000005';
	INSERT INTO test SELECT '00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000006';
	INSERT INTO test SELECT '00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000007';
	INSERT INTO test SELECT '00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000008';
	FOR res_string IN SELECT * FROM test LOOP
		-- Устанавливаем для строки возвращаемой таблицы значения из запроса
		obj_id = res_string.old_id;
		IF (obj_id = '00000000-0000-0000-0000-000000000004') THEN
		BEGIN
			RAISE EXCEPTION USING errcode='IRCER', message='Ошибка', hint='Вызов функции вернул ошибку';
			--break all return tst(), go-to tst2()
		END;
		END IF;
		test_id = res_string.new_id;
		RETURN NEXT;
	END LOOP;
	--......

	RETURN;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION tst2(id1 uuid)
RETURNS TABLE (obj_id uuid, test_id uuid) AS $$
DECLARE
	res_string RECORD;
BEGIN
	RETURN QUERY SELECT * from tst(id1);
	
EXCEPTION WHEN SQLSTATE 'IRCER' THEN
	-- Установка кода "Копирование невозможно"
	obj_id = '00000000-0000-0000-0000-000000000007';
	test_id = '00000000-0000-0000-0000-000000000007';
	RETURN NEXT;
	RETURN;

END;
$$ LANGUAGE plpgsql;

SELECT * FROM tst2('00000000-0000-0000-0000-000000000005')

или же начинать возврат только после , но никак не до обрабатываемого исключения, ветвясь на исключении между 2-мя случаями возвратов. (например набирать будущий возврат в массивы , но не возвращать в случае исключения.

...

Рейтинг:

0 / 0

27.08.2017, 10:09

| Ответить | Цитировать | Написать

Очистка таблицы возвращаемой из функции

#39511114

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

NumberOneДополнительный вопрос, правильно я понимаю, что RAISE EXCEPTION всегда отменяет все действия произведенные функцией, в независимости от того перехватываем мы исключения или нет?
неправильно
во первых не все. во вторых только скл- стейтменты от предыдущего сейвпойнта . предыдущего относительно точки перехвата исключения .
а все, что поменялось в переменных plpgsql -- все там и будет.

то же -- с возвратами. если вы не окончательно упали, ессно. тогда весь возврат накроется. (если был бы пайп и вы из него фетчили, помаленьку, но до ексепшена...
то было бы интересно, как провзаимодействует например return query с предлагаемой оберткой --- там могли бы прорезаться особости реализации пайпов)

RTFM, короче.

...

Рейтинг:

0 / 0

27.08.2017, 10:22

| Ответить | Цитировать | Написать

Очистка таблицы возвращаемой из функции

#39511229

NumberOne

Гость

qwwq,

Спасибо за информацию, пожалуй воспользуюсь способом с многомерным массивом

...

Рейтинг:

0 / 0

27.08.2017, 16:18

| Ответить | Цитировать | Написать

Очистка таблицы возвращаемой из функции

#39511250

NumberOne

Гость

Переделал с массивами. Теперь появился новый вопрос. Как выгоднее сделать возврат результирующей таблицы перед последним RETURN. В рабочей функции у меня 4 массива для хранения полей возвращаемой таблицы, в примере снизу 2 массива.
Можно использовать unnest для всех массивов, как в примере снизу
RETURN QUERY SELECT t.a, t.b FROM (SELECT CAST('00000000-0000-0000-0000-000000000077' AS uuid) AS a, unnest(test_ids) AS b) AS t;
а можно сделать перебор в цикле по массивам, что будет быстрее?

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.

drop TABLE IF EXISTS test ;
CREATE TEMP TABLE test (old_id uuid, new_id uuid);
CREATE OR REPLACE FUNCTION tst(id1 uuid)
RETURNS TABLE (obj_id uuid, test_id uuid) AS $$
DECLARE arr uuid[];
DECLARE arr2 uuid[];
DECLARE res_string RECORD;
DECLARE obj_ids uuid[];
DECLARE test_ids uuid[];
DECLARE row_count int = 0;
BEGIN
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000005';
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000006';
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000007';
    INSERT INTO test SELECT '00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000008';
    FOR res_string IN SELECT * FROM test LOOP
				-- Устанавливаем для строки возвращаемой таблицы значения из запроса
				obj_id = res_string.old_id;
				IF (obj_id = '00000000-0000-0000-0000-000000000004') THEN
				BEGIN
					RAISE EXCEPTION USING errcode='IRCER', message='Ошибка', hint='Вызов функции вернул ошибку';
				END;
				END IF;
				test_id = res_string.new_id;
                row_count = row_count + 1;
				obj_ids[row_count] = obj_id;
				test_ids[row_count] = test_id;
			END LOOP;
    SELECT ARRAY(SELECT old_id FROM test) INTO arr;
    FOR i IN 1..ARRAY_LENGTH(arr, 1) LOOP
    	arr2[i] = uuid_generate_v1();
    END LOOP;
    arr2[3] = NULL;
    arr = (SELECT ARRAY_CAT(arr, (SELECT ARRAY(SELECT old_id FROM test WHERE old_id = ANY (arr)))));
    FOR i IN (ARRAY_LENGTH(arr2, 1)+1)..ARRAY_LENGTH(arr, 1) LOOP
    	arr2[i] = uuid_generate_v1();
    END LOOP;
    RETURN QUERY SELECT t.a, t.b FROM (SELECT CAST('00000000-0000-0000-0000-000000000077' AS uuid) AS a, unnest(test_ids) AS b) AS t;
    RETURN QUERY SELECT t.a, t.b FROM (SELECT unnest(arr) AS a, unnest(arr2) AS b) AS t;
	RETURN;
    EXCEPTION WHEN SQLSTATE 'IRCER' THEN
	BEGIN
        -- Установка кода "Копирование невозможно"
        obj_id = '00000000-0000-0000-0000-000000000007';
        test_id = '00000000-0000-0000-0000-000000000007';
        RETURN NEXT;
    	RETURN;
    END;
    
END; $$ LANGUAGE plpgsql;

...

Рейтинг:

0 / 0

27.08.2017, 18:02

| Ответить | Цитировать | Написать

Очистка таблицы возвращаемой из функции

#39511318

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

NumberOne
а можно сделать перебор в цикле по массивам, что будет быстрее?

дык проверьте.

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

можете например проверить такую штуку:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.

drop TABLE IF EXISTS test ;
CREATE TEMP TABLE test (old_id uuid, new_id uuid);
create or replace function raise_my() RETURNs int 
immutable strict language plpgsql AS
$$
begin
	RAISE EXCEPTION USING errcode='IRCER', message='Ошибка', hint='Вызов функции вернул ошибку';
end;
$$;
CREATE OR REPLACE FUNCTION tst(id1 uuid)
RETURNS TABLE (obj_id uuid, test_id uuid) AS 
$$
DECLARE
	arr uuid[];
	arr2 uuid[];
	res_string RECORD;
	obj_ids uuid[];
	test_ids uuid[];
	row_count int = 0;
	err	boolean;
BEGIN
	INSERT INTO test values
			('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000005')
			,('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000006')
			,('00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000007')
			,('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000008')
			;

	--SELECT array_agg(old_id),array_agg(old_id),bool_or(old_id = '00000000-0000-0000-0000-000000000004'),count(1)
	-- лучше, если записей много ~  bool_or(case when old_id = '00000000-0000-0000-0000-000000000004' THEN 1/0=1 END )  -- и ловить исключение /0
	SELECT array_agg(old_id),array_agg(old_id),sum(case when old_id = '00000000-0000-0000-0000-000000000004' THEN raise_my() END),count(1)
	INTO	obj_ids		,test_ids	,err ,row_count
	FROM test;

	SELECT array_agg(old_id) ,array_agg(uuid_generate_v1()) 
	INTO arr,arr2
	FROM test;
	-- тут пошля какая-то безумная хня, я не буду её переписыват с лупов на аггрегаты
	arr2[3] = NULL;
	arr = (SELECT ARRAY_CAT(arr, (SELECT ARRAY(SELECT old_id FROM test WHERE old_id = ANY (arr)))));
	FOR i IN (ARRAY_LENGTH(arr2, 1)+1)..ARRAY_LENGTH(arr, 1) LOOP
		arr2[i] = uuid_generate_v1();
	END LOOP;
	--/ тут пошля какая-то хня, я не буду её переписыват с лупов на аггрегаты
	EXCEPTION WHEN SQLSTATE 'IRCER' THEN
	--EXCEPTION WHEN others THEN
	BEGIN
		-- Установка кода "Копирование невозможно"
		obj_id = '00000000-0000-0000-0000-000000000007';
		test_id = '00000000-0000-0000-0000-000000000007';
		RETURN NEXT;
		RETURN;
	END;
	-- не падали => выводим накопленное
	RETURN QUERY SELECT t.a, t.b FROM (SELECT CAST('00000000-0000-0000-0000-000000000077' AS uuid) AS a, unnest(test_ids) AS b) AS t;
	--RETURN QUERY SELECT t.a, t.b FROM (SELECT unnest(arr) AS a, unnest(arr2) AS b) AS t; -- тут надо понимать, что будет при различной кардинальности
	-- сравнить по скорости с  
	RETURN QUERY SELECT t.a, t.b FROM unnest(arr ,arr2)  t (a,b); -- гарантия однократного обхода при разных кардинальностях
	RETURN;

	
END; $$ LANGUAGE plpgsql;

explain analyze SELECT tst('00000000-0000-0000-0000-000000000005') ,generate_series(0,10000);

супротив вашей:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.

drop TABLE IF EXISTS test ;
CREATE TEMP TABLE test (old_id uuid, new_id uuid);
CREATE OR REPLACE FUNCTION tst(id1 uuid)
RETURNS TABLE (obj_id uuid, test_id uuid) AS $$
DECLARE arr uuid[];
DECLARE arr2 uuid[];
DECLARE res_string RECORD;
DECLARE obj_ids uuid[];
DECLARE test_ids uuid[];
DECLARE row_count int = 0;
BEGIN
	INSERT INTO test values
			('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000005')
			,('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000006')
			,('00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000007')
			,('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000008')
			;
		    FOR res_string IN SELECT * FROM test LOOP
				-- Устанавливаем для строки возвращаемой таблицы значения из запроса
				obj_id = res_string.old_id;
				IF (obj_id = '00000000-0000-0000-0000-000000000004') THEN
				BEGIN
					RAISE EXCEPTION USING errcode='IRCER', message='Ошибка', hint='Вызов функции вернул ошибку';
				END;
				END IF;
				test_id = res_string.new_id;
                row_count = row_count + 1;
				obj_ids[row_count] = obj_id;
				test_ids[row_count] = test_id;
			END LOOP;
    SELECT ARRAY(SELECT old_id FROM test) INTO arr;
    FOR i IN 1..ARRAY_LENGTH(arr, 1) LOOP
    	arr2[i] = uuid_generate_v1();
    END LOOP;
    arr2[3] = NULL;
    arr = (SELECT ARRAY_CAT(arr, (SELECT ARRAY(SELECT old_id FROM test WHERE old_id = ANY (arr)))));
    FOR i IN (ARRAY_LENGTH(arr2, 1)+1)..ARRAY_LENGTH(arr, 1) LOOP
    	arr2[i] = uuid_generate_v1();
    END LOOP;
    RETURN QUERY SELECT t.a, t.b FROM (SELECT CAST('00000000-0000-0000-0000-000000000077' AS uuid) AS a, unnest(test_ids) AS b) AS t;
    RETURN QUERY SELECT t.a, t.b FROM (SELECT unnest(arr) AS a, unnest(arr2) AS b) AS t;
	RETURN;
    EXCEPTION WHEN SQLSTATE 'IRCER' THEN
	--Установка кода "Копирование невозможно"
        obj_id = '00000000-0000-0000-0000-000000000007';
        test_id = '00000000-0000-0000-0000-000000000007';
        RETURN NEXT;
    	RETURN;

END; $$ LANGUAGE plpgsql;

explain analyze SELECT tst('00000000-0000-0000-0000-000000000005') ,generate_series(0,10000);

--всего-то 10000 лишних присвоений , казалось бы. (агргегаты там вообще не играют роли, думается)

...

Рейтинг:

0 / 0

27.08.2017, 22:25

| Ответить | Цитировать | Написать

Очистка таблицы возвращаемой из функции

#39511348

NumberOne

Гость

qwwq,
спасибо, буду разбираться, что тут происходит

...

Рейтинг:

0 / 0

28.08.2017, 02:01

| Ответить | Цитировать | Написать

7 сообщений из 7, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Очистка таблицы возвращаемой из функции

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&fpage=67&tid=1996265]:	0ms
get settings:	8ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	35ms
get topic data:	14ms
get forum data:	3ms
get page messages:	42ms
get tp. blocked users:	1ms
others:	16ms

total:	141ms