Passing table name to function Dynamically as Input parameter and using it

Question

In the below function I am passing in my table name as text 'K' and wish to use it for cursor declaration inside my function.I am using postgres 8.2 (greenplum)

I used quote_ident(k), dint work either. Could some one help me out. THanks!

CREATE OR REPLACE FUNCTION vin_temp(K text) RETURNS text AS
    $BODY$
    DECLARE
        r vin_res%rowtype;
        r1 numeric default 0;

        rfr numeric default 0;
        rdu numeric default 0;
        rav numeric default 0;

        rfr1 numeric default 0;
        rdu1 numeric default 0;
        rav1 numeric default 0;


        r2 numeric default 0;
        i integer default 0;
        x  text default '';
        curs2 CURSOR FOR SELECT * FROM k order by week_end asc;
    BEGIN

    open curs2;
      LOOP
      FETCH curs2 INTO r;
      exit when not found;

         if (i=1) then
          r1:=r.v1;
          rav:=r.v2;
          rfr:=r.v3;
          else 
          some logic here
          end if;

        END LOOP;
        RETURN 'yes';
    END
    $BODY$
    LANGUAGE 'plpgsql' ;

i tried the following code as well :

curs2 CURSOR FOR EXECUTE 'SELECT * from '||quote_ident(k)||' order by week_end asc';

Answer 1

An unbound cursor must be used for dynamic queries.
It is described in documentation for version 8.2 :
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html

37.8.2.2. OPEN FOR EXECUTE

OPEN unbound_cursor FOR EXECUTE query_string;

The cursor variable is opened and given the specified query to execute. The cursor cannot be open already, and it must have been declared as an unbound cursor (that is, as a simple refcursor variable). The query is specified as a string expression, in the same way as in the EXECUTE command. As usual, this gives flexibility so the query can vary from one run to the next.

An example:

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

(emphasis mine)

Here is a working example, please take a look at this link: http://sqlfiddle.com/#!11/a0d8a/1

CREATE OR REPLACE FUNCTION vin_temp(K text) RETURNS int 
AS
$BODY$
DECLARE
  curs1 refcursor;
  x int;
begin
  OPEN curs1 FOR EXECUTE 'Select Sum( x ) From ' || $1;
  Fetch curs1 Into x;
  Close curs1;
  Return x;
End
$BODY$ LANGUAGE 'plpgsql' 
/