postgresql - 在不使用预定义表的情况下在 postgresql 中循环查询结果

Question

我创建了一个循环查询结果的函数，它工作正常。我想让它工作而不必预先创建一个表来存储结果。即，我只想遍历行。问题是，我不确定如何在没有可检查的情况下声明与返回的行类型相同的记录。

这是我当前的功能:

CREATE OR REPLACE FUNCTION dimensions.testing()
RETURNS void
LANGUAGE plpgsql
AS $body$
DECLARE rec myschema.tmpfiles%rowtype;
BEGIN

insert into myschema.tmpfiles(file_name, log_date)  
    SELECT f.file_name, f.log_date from dblink('conn', 'select 
file_name, log_date from myschema.process_tracker where 
isprocessed = FALSE') as f(file_name varchar, log_date date);

IF EXISTS (SELECT 1 FROM myschema.tmpfiles) THEN
    for rec in select * from myschema.tmpfiles
loop
        RAISE NOTICE '%', rec.file_name;
        RAISE NOTICE '%', rec.log_date;
        RAISE NOTICE '---------------------------';
end loop;           
ELSE
    --DO SOMETHING
END IF;

END;
$body$

工作正常，但它需要一个预定义的表以便我执行此操作:(DECLARE rec myschema.tmpfiles%rowtype;) 并获取行类型。

如何在不预先定义结果表的情况下循环此查询？

谢谢!

Answer 1

RECORD 类型是一个没有预定义结构的行型变量。

您通过执行 name RECORD;

声明它

Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. A consequence of this is that until a record variable is first assigned to, it has no substructure, and any attempt to access a field in it will draw a run-time error.

将 myschema.tmpfiles%rowtype 更改为 RECORD，您应该没问题。