1

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?

1 Answer 1

3

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:

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:

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:

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

3 Comments

I tried this. actually I want to use the input for cursor (in declare block).
@Spike: I added another answer how to use a cursor for this.
Working fine. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.