How to pass a table name for a cursor as function parameter?

Question

In PostgreSQL 9.1, I need to pass a table name as parameter to my function to be used in a cursor - and then call the function like:

select myfunction('tableA');

or:

select myfunction('tableB')

I have tried this:

 create or replace function myfunction(sid text) 
 returns integer as $BODY$
 declare
      bids cursor is select id from sid; --line 4
 begin
      for bid in bids loop
          --some logic
      end loop;
      return 1;
 end;
 $BODY$
 language plpgsql volatile;

I am getting error on line 4. And I understood it, it's not allowed to pass a table name as function parameter where it's allowed rest (selection, projection).

Are there alternatives to achieve this?

Answer 1

Only values can be parameterized in SQL commands, not identifiers or key words. And it currently works only for SELECT, INSERT, UPDATE, and DELETE commands. Details:

• Error when setting n_distinct using a plpgsql variable

You need dynamic SQL with EXECUTE in plpgsql.
Combine that with the implicit cursor of a FOR loop, which is typically simpler and faster than an explicit cursor. Instructions in the manual.

I also suggest to use a regclass parameter to pass valid table names safely.

CREATE OR REPLACE FUNCTION myfunction(_tbl regclass) 
  RETURNS int  AS
$func$
DECLARE
   bid integer;  -- appropriate data type!
BEGIN
   FOR bid IN EXECUTE
      'SELECT id FROM ' || _tbl
   LOOP
      -- statements can use bid
      RAISE NOTICE '%', bid;
   END LOOP;

   RETURN 1;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT myfunction('tableA');  -- careful: case sensitive!

More explanation:

• Table name as a PostgreSQL function parameter

And typically, there is a faster set-based solution that does not need to loop at all.

---

If you positively need a cursor, declare an unbound cursor and open it with OPEN FOR EXECUTE:

CREATE OR REPLACE FUNCTION myfunction(_tbl text)  -- could be regclass again
  RETURNS int AS
$func$
DECLARE
   _bids refcursor;
   _rec  record;
BEGIN
   OPEN _bids FOR EXECUTE 'SELECT id FROM ' || quote_ident(_tbl);  -- text must be escaped

   LOOP
      FETCH NEXT FROM _bids INTO _rec;
      EXIT WHEN _rec IS NULL;

      -- some logic
      RAISE NOTICE '%', _rec.id;
   END LOOP;

   RETURN 1;
END
$func$  LANGUAGE plpgsql;

Same call. More details in this closely related answer:

• Update record of a cursor where the table name is a parameter