For loop with dynamic table name in Postgresql 9.1?

dynamic-sql, postgresql

Solution

You cannot use a variable as table or column identifier in plpgsql embedded SQL ever. A solution is dynamic SQL - EXECUTE or FOR IN EXECUTE statements:

DO $$
DECLARE
  tables text[] = ARRAY['table1','table2'];
  table_name text;
  rec record;
BEGIN
  FOREACH table_name IN ARRAY tables
  LOOP
    FOR r IN EXECUTE format('SELECT * FROM %I', table_name)
    LOOP
      RAISE NOTICE '%', rec;
    END LOOP; 
  END LOOP;
END; $$

Problem

I have a `plpgslq` function which does some data processing and would like to write a `for` loop, however my table name is not known at design time. Is there any possible way to achieve this? Here is sample code snippet of what I want to achieve: ``` -- Function: check_data() -- DROP FUNCTION check_data(); CREATE OR REPLACE FUNCTION check_data() RETURNS character varying AS $BODY$declare dyn_rec record; tbl_name record; begin -- sample dynamic tables tbl_name := 'cars'; tbl_name := 'trucks'; tbl_name := 'bicycles'; for dyn_rec in select * from format($$s%$$,tbl_name) loop raise notice 'item is %',dyn_rec.item_no; end loop; return 'Processing Ok'; end;$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION check_data() OWNER TO postgres; ```

Original source