Save SQL query to variable

Question

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).

i believe you should use cursor here

Vlad Khomich
– Vlad Khomich

2011-04-19 12:11:39 +00:00
Commented Apr 19, 2011 at 12:11 — Vlad Khomich
– Vlad Khomich, Commented Apr 19, 2011 at 12:11

user330315 · Accepted Answer · 2011-04-19 12:14:32Z

3

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;

answered Apr 19, 2011 at 12:14

user330315

Sign up to request clarification or add additional context in comments.

Comments

Vlad Khomich · Accepted Answer · 2011-04-19 12:15:06Z

3

DECLARE

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

FOR cursor_rec in cursor LOOP
  ... 
END LOOP;

answered Apr 19, 2011 at 12:15

Vlad Khomich

5,9001 gold badge30 silver badges40 bronze badges

Comments

Alaa · Accepted Answer · 2011-04-19 12:30:07Z

-1

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

edited Apr 19, 2011 at 12:30

answered Apr 19, 2011 at 12:23

Alaa

5851 gold badge5 silver badges11 bronze badges

Collectives™ on Stack Overflow

Save SQL query to variable

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related