Dynamically generated CURSOR in Postgresql

Question

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;

Any ideas?

Thank you.

Pavel Stehule · Accepted Answer · 2017-07-04 17:51:30Z

11

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

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 $$;

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

edited Jul 4, 2017 at 17:51

answered Dec 28, 2015 at 17:20

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

2 Comments

Za7pi Over a year ago

OPEN FOR EXECUTE is the one i need. Thanks

HeikkiVesanto Over a year ago

I believe you will also have to declare the rec variable: rec record;

Collectives™ on Stack Overflow

Dynamically generated CURSOR in Postgresql

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related