plpgsql函数中IF EXISTS内部标识符的变量 [英] Variables for identifiers inside IF EXISTS in a plpgsql function

问题描述

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;

您能告诉我如何在IF EXISTS内部的SELECT中使用变量吗?目前，row.relname.tm和row.relname的字面意思是我不想要的.

Could you tell me how to use variables in SELECT which is inside IF EXISTS? At the present moment, row.relname.tm and row.relname are treated literally which is not I want.

推荐答案

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变量前加下划线_以避免任何命名冲突.

  Major points

  • row is a reserved word in the SQL standard. It's use is allowed in Postgres, but it's still unwise. I make it a habbit to prepend psql variable with an underscore _ to avoid any naming conflicts.

    无论如何，您不会选择整个行，在此示例中仅选择表名.最好使用类型为regclass的变量，从而自动避免使用非法表名进行SQL注入.相关答案中的详细信息:
    表名作为PostgreSQL函数参数

    You don't don't select the whole row anyway, just the table name in this example. Best use a variable of type regclass, thereby avoiding SQL injection by way of illegal table names automatically. Details in this related answer:
    Table name as a PostgreSQL function parameter

    在EXISTS表达式中不需要LIMIT，该表达式仅检查 any 行的存在.出于相同的原因，您不需要有意义的目标列.只需编写 SELECT 1或SELECT *之类的东西.

    You don't need LIMIT in an EXISTS expression, which only checks for the existence of any rows. And you don't need meaningful target columns for the same reason. Just write SELECT 1 or SELECT * or something.

    对于具有可变标识符的查询，您需要动态SQL .普通SQL不允许这样做.即:构建查询字符串并EXECUTE进行查询.此密切相关的答案中的详细信息:
    将动态SQL(EXECUTE)作为IF语句的条件

    You need dynamic SQL for queries with variable identifiers. Plain SQL does not allow for that. I.e.: build a query string and EXECUTE it. Details in this closely related answer:
    Dynamic SQL (EXECUTE) as condition for IF statement

    如果要运行DROP语句，则同样如此.我添加了一条评论.

    The same is true for a DROP statement, should you want to run it. I added a comment.

    这篇关于plpgsql函数中IF EXISTS内部标识符的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！