For loop with dynamic table name in Postgresql 9.1?

Question

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;

yunque · Accepted Answer · 2018-11-02 12:23:39Z

18

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

edited Nov 2, 2018 at 12:23

yunque

6851 gold badge8 silver badges18 bronze badges

answered Jan 7, 2014 at 8:31

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

arcull Over a year ago

Thanks Pavel Stehule, 'execute' is exactly what I need, works ok now.

425nesp Over a year ago

arcull, perhaps you should mark this as the answer if it worked. :)

Collectives™ on Stack Overflow

For loop with dynamic table name in Postgresql 9.1?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related