将 SQL 查询保存到变量

发布于 2024-11-01 23:29:55 字数 383 浏览 1 评论 0原文

我已经编写了一个 PL/PgSQL 触发器，我需要将查询（实际上是结果集）保存到变量。请参阅下文：

DECLARE
    __query record;
    r record;
BEGIN
    __query := (SELECT * FROM posts);
    FOR r IN __query LOOP
        -- do something with the row data
    END LOOP;

    RETURN NEW;
END;

我应该使用哪种数据类型进行查询本身？

我猜 record 不是合适的数据类型，应该在循环周期本身中使用（对于 r var）。

原文

I have written a PL/PgSQL trigger and i need to save the query (in fact the result set) to variable.
See below:

DECLARE
    __query record;
    r record;
BEGIN
    __query := (SELECT * FROM posts);
    FOR r IN __query LOOP
        -- do something with the row data
    END LOOP;

    RETURN NEW;
END;

Which data type for query itself should i use?

I guess record is not appropriate data type and should be used in the loop cycle itself (for r var).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梅窗月明清似水 2024-11-08 23:29:55

如果您的意思是要将循环查询作为字符变量传递，那么您可以这样做：

DECLARE 
    _query : text;
    r : record;
BEGIN
    _query := 'SELECT * FROM posts';

    FOR r IN EXECUTE _query LOOP
       -- do stuff
    END LOOP;

    RETURN new;
END;

If you mean you want to pass the query for the loop as a character variable, then you can do it like this:

DECLARE 
    _query : text;
    r : record;
BEGIN
    _query := 'SELECT * FROM posts';

    FOR r IN EXECUTE _query LOOP
       -- do stuff
    END LOOP;

    RETURN new;
END;

回复收藏 0 原文

笑，眼淚并存 2024-11-08 23:29:55

DECLARE

  CURSOR cursor is (select * from posts);
  r      record;
BEGIN

FOR cursor_rec in cursor LOOP
  ... 
END LOOP;

DECLARE

  CURSOR cursor is (select * from posts);
  r      record;
BEGIN

FOR cursor_rec in cursor LOOP
  ... 
END LOOP;

回复收藏 0 原文

柠檬色的秋千 2024-11-08 23:29:55

DECLARE

  cursor c is select * from posts;
  -- r  record; you don't need to declare this variable
BEGIN
   FOR c_rec in c LOOP
       ... 
       ... 
       ... 
   END LOOP;
END;

oracle pl-sql 的这段代码

DECLARE

  cursor c is select * from posts;
  -- r  record; you don't need to declare this variable
BEGIN
   FOR c_rec in c LOOP
       ... 
       ... 
       ... 
   END LOOP;
END;

this code for oracle pl-sql

回复收藏 0 原文

~没有更多了~