在Postgresql中动态生成的CURSOR [英] Dynamically generated CURSOR in Postgresql

问题描述

我有一个游标，它指向一个SELECT，但是此选择是动态生成的.我想在声明后分配声明. 我已经完成了一个示例工作，而另一个示例不工作.这是仅打印一些数据的简单示例. 这是表格:

I have got a cursor, it is pointing to a SELECT, but this select is generated dynamically. I want to assign the statement after the declarement. I have done an example working and another example NOT working. This is a simple example to print some data only. This is the table:

CREATE TABLE public.my_columns (
  id      serial NOT NULL,
  "name"  varchar(30) NOT NULL,
  /* Keys */
  CONSTRAINT my_columns_pkey
    PRIMARY KEY (id)
) WITH (
    OIDS = FALSE
  );

CREATE INDEX my_columns_index01
  ON public.my_columns
  ("name");


INSERT INTO public.my_columns
    ("name")
VALUES
    ('name1'),
    ('name2'),
    ('name3'),
    ('name4'),
    ('name5'),
    ('name6');

这是函数(我已经把有效的代码和无效的代码放进去了):

This is the function(I have put the working code and the code not working):

CREATE OR REPLACE FUNCTION public.dynamic_table
(
)
RETURNS text AS $$
DECLARE
       v_sql_dynamic varchar;

       --NOT WORKING:
       --db_c CURSOR IS (v_sql_dynamic::varchar);

       --WORKING:
       db_c CURSOR IS (SELECT id, name from public.my_columns);

       db_rec RECORD;

BEGIN
     v_sql_dynamic := 'SELECT id, name from public.my_columns';
    FOR db_rec IN db_c LOOP

        RAISE NOTICE 'NAME: %', db_rec.name;
    END LOOP;
    RETURN 'OK';

EXCEPTION WHEN others THEN

    RETURN 'Error: ' || SQLERRM::text || ' ' || SQLSTATE::text;
END; 
$$ LANGUAGE plpgsql;

有什么想法吗?

谢谢.

推荐答案

您真的需要显式游标吗?如果您需要遍历动态SQL，则可以使用FOR IN EXECUTE.它是动态SQL的隐式(内部)游标的循环

Do you really need the explicit cursor? If you need iterate over dynamic SQL, then you can use FOR IN EXECUTE. It is loop over implicit (internal) cursor for dynamic SQL

FOR db_rec IN EXECUTE v_sql_dynamic
LOOP
  ..
END LOOP

文档中介绍了一些更复杂的解决方案-OPEN FOR EXECUTE:

Little bit more complex solution is described in documentation - OPEN FOR EXECUTE:

do $$
declare r refcursor; rec record;
begin 
  open r for execute 'select * from pg_class'; 
  fetch next from r into rec;
  while found 
  loop
    raise notice '%', rec; 
    fetch next from r into rec; 
  end loop;
  close r; 
end $$;

使用这种光标，您将无法使用FOR IN

With this kind of cursor, you cannot to use FOR IN

这篇关于在Postgresql中动态生成的CURSOR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！