Проверить массив имен таблиц
У меня есть требование, чтобы сценарий bash передавал строку, содержащую имена таблиц, в анонимный блок PL/pgSQL, который будет обрабатывать таблицы, если они существуют в базе данных Postgres.
Ниже приведен код PL/pgSQL, предполагающий, что переменная заданных_таблиц содержит строку, состоящую из имен таблиц, предоставленных с помощью сценария bash.
do $do$
<<outerblock>>
declare
tab text;
given_tables text[] := ARRAY['ocab1.cust_docu_map','ocsbc2.cust_docu_map'];
table_names text[] := ARRAY(
BEGIN
FOREACH tab IN ARRAY given_tables loop
IF EXISTS
( SELECT 1
FROM pg_class
WHERE oid = tab::regclass
)
THEN
SELECT concat_ws('.',relnamespace::regnamespace::text,relname)
FROM pg_class
WHERE oid = tab::regclass
AND relreplident != 'f';
END IF;
END LOOP;
END
);
begin
foreach table in array table_names loop
raise notice 'here is the table - %',outerblock.table;
end loop;
end $do$;
Но ошибки кода:
ERROR: mismatched parentheses at or near ";" LINE 18: AND relreplident != 'f'; ^
Не знаете, где я ошибаюсь?
Но мне нужен массив для обработки окончательного списка таблиц, который существует в базе данных после проверки. Пожалуйста, направляйте
Ответы 2
Этот код недействителен, вы не можете создать такой массив. И оно вам тоже не понадобится, просто выполните запрос на получение таблиц из pg_class. Что-то вроде этого:
DO
$do$
DECLARE
given_tables TEXT[] = ARRAY ['ocab1.cust_docu_map','ocsbc2.cust_docu_map'];
_tab TEXT;
_row RECORD;
BEGIN
FOREACH _tab IN ARRAY given_tables
LOOP
FOR _row IN
SELECT CONCAT_WS('.', relnamespace::REGNAMESPACE::TEXT, relname) AS t
FROM pg_class
WHERE oid = _tab::REGCLASS -- error when table doesn't exist!
AND relreplident != 'f'
LOOP
RAISE NOTICE 'here is the table - %', _row.t;
END LOOP;
END LOOP;
END
$do$;
Будьте осторожны: этот запрос завершается ошибкой при поиске несуществующей таблицы. Вам лучше запросить имя relname и имя схемы. В этом случае вы просто не получите результата.
DO
$do$
DECLARE
_given_tbls text[] := '{ocab1.cust_docu_map,ocsbc2.cust_docu_map}';
_tbl text;
BEGIN
FOR _tbl IN
SELECT format('%I.%I', n.nspname, c.relname) -- properly quoted
FROM unnest(_given_tbls) t(tbl)
JOIN pg_class c ON c.oid = to_regclass(t.tbl) -- does not raise exception
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relreplident != 'f'
LOOP
RAISE NOTICE 'valid table: %', _tbl;
-- or do something here!
END LOOP;
END
$do$;
to_regclass(_tab) не вызывает исключения для недопустимых имен таблиц. Видеть:
Помните, что идентификаторы, хранящиеся в таблицах каталога, могут нуждаться в двойных кавычках. Используйте format() или quote_ident() в кавычках для защиты от синтаксических ошибок или даже атак SQL-инъекций.
Значения regclass заключаются автоматически при преобразовании в текст, но только при необходимости с указанием схемы с текущим search_path. Кажется, вам нужны полные имена. Видеть:
Кроме того, гораздо дешевле выполнить один запрос и просмотреть результаты (если вам вообще нужен цикл?), чем запускать другой запрос (или даже два!) для каждого элемента массива.
Спасибо за to_regclass(), не знал этой функции. Спасибо!
Я почти уверен, что вы не можете использовать конструкции
plpgsqlвнутриARRAY(). Почему бы просто не вытащить этоLOOPизARRAY()и не выполнитьraise notice 'here is the table - %'в цикле.