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.

Community · Accepted Answer · 2017-05-23 11:59:25Z

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 ":="

Tom-db · Accepted Answer · 2016-02-26 10:58:02Z

0

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;

edited Feb 26, 2016 at 10:58

answered Feb 25, 2016 at 20:57

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

1 Comment

Harmonic4352 Over a year ago

Thank you, this was helpful.

Collectives™ on Stack Overflow

How to pass table name to plpgsql function

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related