我想做这样的事情,但不确定用于在表上迭代for 循环的数据类型记录的行变量是否可以插入到同一个 for 循环本身内的临时表中,
CREATE OR REPLACE FUNCTION LoopThroughTable()
RETURNS VOID
AS
$$
DECLARE
t_row record;
BEGIN
FOR t_row in SELECT * FROM the_table
LOOP
create temp table tmp as (select * from t_row); --- is this possible??
--- use the data of tmp table in some select query
drop table tmp; -- is this possible??
END LOOP;
END;
$$
LANGUAGE plpgsql;
如果可能的话,有人可以帮助我正确的语法吗?
想通了!
以防万一有人偶然发现了这篇文章,访问t_row记录类型的数据的有效方法是:
CREATE OR REPLACE FUNCTION LoopThroughTable()
RETURNS VOID
AS
$$
DECLARE
t_row record;
BEGIN
FOR t_row in SELECT * FROM table_1
LOOP
SELECT * FROM table_2 when A= t_row.B
-- where A is a column in table_2 whose value should be equal to column B in t_row (which single row from table_1)
END LOOP;
END;
$$
LANGUAGE plpgsql;