How to pass table name to plpgsql function

Question

I am trying to run the code:

CREATE OR REPLACE FUNCTION anly_work_tbls.testfncjh (tablename text) returns int
AS $$
DECLARE 
        counter int;
        rec record;
        tname text;
BEGIN
counter = 0;
tname := tablename; 
FOR rec IN
        select *
        from tname
    loop
        counter = counter + 1;
    end loop;
RETURN counter;
END; 
$$ 
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER;

The goal of this code is to return the number of rows in the table you input. I know that this might not be the best way to accomplish this task, but the structure of this function would extend nicely to another question I am trying to tackle. Every time I run the code, I get the error:

  ERROR: syntax error at or near "$1"

All online resources I have found tell me how to use the input variable within and EXECUTE block, but not in the above situation.

Currently running PostgreSQL 8.2.15.

Answer 1

Yes is really not the best way, but this would work:

CREATE OR REPLACE FUNCTION testfncjh (tablename text) returns int
AS $$
DECLARE 
        counter int;
        rec record;        
BEGIN
counter = 0;
FOR rec IN
        EXECUTE 'select * from '||quote_ident(tablename) loop
        counter = counter + 1;
    end loop;
RETURN counter;
END; 
$$ 
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER;

This would be nicer:

CREATE OR REPLACE FUNCTION testfncjh (tablename text) returns int
AS $$
DECLARE _count INT;
BEGIN    
 EXECUTE 'SELECT count(*) FROM '|| quote_ident(tablename) INTO _count;
 RETURN _count;
END; 
$$ 
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER;

Answer 2

CREATE OR REPLACE FUNCTION anly_work_tbls.testfncjh (tbl regclass, OUT row_ct int) AS
$func$
BEGIN    
   EXECUTE 'SELECT count(*) FROM '|| tbl
   INTO row_ct;
END
$func$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER;

Call:

SELECT anly_work_tbls.testfncjh('anly_work_tbls.text_tbl');

This should work for Postgres 8.2, but you consider upgrading to a current version anyway.

• I pass the table name as object identifier type regclass, which takes care of quoting automatically and works with schema-qualified names. Details:
  • Table name as a PostgreSQL function parameter

• Using an OUT parameter simplifies the function.

• Don't quote the language name. It's an identifier.

If you actually need to loop through the result of a dynamic query:

CREATE OR REPLACE FUNCTION anly_work_tbls.testfncjh (tbl regclass)
  RETURNS int AS
$func$
DECLARE 
   counter int := 0;  -- init at declaration time
   rec record;
BEGIN
   FOR rec IN EXECUTE
      'SELECT * FROM ' || tbl
   LOOP
      counter := counter + 1;  -- placeholder for some serious action
   END LOOP;

   RETURN counter;
END
$func$  LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER;

• Read this chapter in the manual: Looping Through Query Results

• The documented assignment operator in plpgsql is :=:

  • The forgotten assignment operator "=" and the commonplace ":="