Доброе утро. DO $$ DECLARE rec RECORD; BEGIN FOR

Question

Доброе утро. DO $$ DECLARE rec RECORD; BEGIN FOR

rec IN SELECT type_street,
label_street,
min(id) AS keep_id,
array_remove(array_agg(id), min(id)) discard_ids
FROM street_type
GROUP BY type_street, label_street
HAVING count(*) > 1
LOOP
UPDATE "addr_RF" SET street_type_id = rec.keep_id WHERE street_type_id IN (unnest(rec.discard_ids));
DELETE FROM street_type WHERE id IN (unnest(rec.discard_ids));
END LOOP;
END
$$ LANGUAGE plpgsql;

[2019-06-07 11:17:05] [0A000] ERROR: set-returning functions are not allowed in WHERE
[2019-06-07 11:17:05] Где: PL/pgSQL function inline_code_block line 14 at SQL statement

Читал, что с 10 версии PG такие изменения в select set-returning. Как под это подстроиться?

#backend #devops #pgsql #programming #russian

0

07.06.2019

1 ответов

13 просмотров

Yaroslav Schekin · Accepted Answer

Yaroslav Schekin

WHERE street_type_id IN (SELECT unnest(rec.discard_ids)); Или WHERE street_type_id = ANY (rec.discard_ids);

0

07.06.2019

172 похожих чатов

Доброе утро. DO $$ DECLARE rec RECORD; BEGIN FOR

1 ответов

Похожие вопросы