CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sqldatabase-cursor
user2664380
user2664380

Reputation: 289

Parameterize table name for cursor bound variable

Below is the function where the records are stored in a record variable for each iteration. Here the table name is hardcoded for cursor bound variable. Is there is any way I can pass the table name as a parameter through this function?

CREATE OR REPLACE FUNCTION test1()
  RETURNS SETOF refcursor AS 
$BODY$
DECLARE
   curs2 CURSOR FOR SELECT * FROM datas.test1000;
begin

FOR recordvar IN curs2 LOOP
   RAISE NOTICE 'recordvar: %',recordvar; 
END LOOP ;

end;
$BODY$
language plpgsql;

Upvotes: 4

Views: 2627

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657982

No, not for a bound cursor.

But you can easily pass a name for opening an unbound cursor. There is an example in the manual doing precisely that.

Your function could look like this:

CREATE OR REPLACE FUNCTION test2(_tbl regclass)
  RETURNS void AS 
$func$
DECLARE
   _curs refcursor;
   rec   record;
BEGIN

OPEN _curs FOR EXECUTE
 'SELECT * FROM ' || _tbl;

LOOP
   FETCH NEXT FROM _curs INTO rec;
   EXIT WHEN rec IS NULL;

   RAISE NOTICE 'rec: %', rec; 
END LOOP;

END
$func$ language plpgsql;

The special FOR loop can only be used with bound cursors. I supplied an alternative.
More explanation in this closely related answer:
Update record of a cursor where the table name is a parameter

I use the object identifier type regclass to pass the table name to avoid SQL injection.
More about that in this related answer on dba.SE:

Upvotes: 4

Related Questions