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