Переменные для идентификаторов внутри IF EXISTS в функции plpgsql

CREATE OR REPLACE FUNCTION drop_now()
  RETURNS void AS
$BODY$
DECLARE
    row     record;
BEGIN
    RAISE INFO 'in';
    FOR row IN 
        select relname from pg_stat_user_tables
        WHERE schemaname='public' AND relname LIKE '%test%'
    LOOP    
    IF EXISTS(SELECT row.relname.tm FROM row.relname
              WHERE row.relname.tm < current_timestamp - INTERVAL '90 minutes'
              LIMIT 1)              
    THEN
    -- EXECUTE 'DROP TABLE ' || quote_ident(row.relname);
    RAISE INFO 'Dropped table: %', quote_ident(row.relname);

    END IF;

    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Не могли бы вы рассказать мне, как использовать переменные в SELECT, который находится внутри IF EXISTS? В настоящий момент row.relname.tm и row.relname обрабатываются буквально, чего я не хочу.


postgresql dynamic-sql exists plpgsql postgresql-9.1
person Tom    schedule 25.11.2013    source источник

Ответы (2)


arrow_upward
1
arrow_downward 

CREATE OR REPLACE FUNCTION drop_now()
  RETURNS void AS
$func$
DECLARE
   _tbl   regclass;
   _found int;
BEGIN
   FOR _tbl IN 
      SELECT relid
      FROM   pg_stat_user_tables
      WHERE  schemaname = 'public'
      AND    relname LIKE '%test%'
   LOOP
      EXECUTE format($f$SELECT 1 FROM %s
                        WHERE  tm < now() - interval '90 min'$f$, _tbl);
      GET DIAGNOSTICS _found = ROW_COUNT;
      IF _found > 0 THEN
         -- EXECUTE 'DROP TABLE ' || _tbl;
         RAISE NOTICE 'Dropped table: %', _tbl;
      END IF;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Основные моменты

  • row — это зарезервированное слово в стандарте SQL. Его использование разрешено в Postgres, но все равно неразумно. Я сделал привычкой добавлять перед переменной psql знак подчеркивания _, чтобы избежать конфликтов имен.

  • В любом случае вы не выбираете всю строку, а только имя таблицы в этом примере. Лучше всего использовать переменную типа regclass, тем самым автоматически избегая SQL-инъекций посредством недопустимых имен таблиц. Подробности в этом связанном ответе:
    Имя таблицы как PostgreSQL параметр функции

  • Вам не нужно LIMIT в выражении EXISTS, которое только проверяет наличие любых строк. И вам не нужны осмысленные целевые столбцы по той же причине. Просто напишите SELECT 1 или SELECT * или что-то в этом роде.

  • Вам нужен динамический SQL для запросов с переменными идентификаторами. Обычный SQL не позволяет этого. То есть: построить строку запроса и EXECUTE ее. Подробности в этом тесно связанном ответе:
    Динамический SQL (EXECUTE) как условие для оператора IF

  • То же самое верно и для оператора DROP, если вы захотите его запустить. Я добавил комментарий.

person Erwin Brandstetter    schedule 25.11.2013
comment
Спасибо за пояснения! - person Tom; 26.11.2013
comment
Один вопрос: что $f$ делает в format? Не могу найти в документах. - person Tom; 26.11.2013
comment
@Tom: это вложенный доллар -цитирование. - person Erwin Brandstetter; 26.11.2013

arrow_upward
0
arrow_downward

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

person yieldsfalsehood    schedule 25.11.2013