Geek Answers Handbook

How to choose from a variable that is a table name n Postgre> = 9.2

I have a variable that is the name of a table. How can I select or update this using a variable in a query, for example:

create or replace function pg_temp.testtst ()
returns varchar(255) as 
$$
declare 
r record; t_name name;
begin   
  for r in SELECT tablename FROM pg_tables WHERE schemaname = 'public' limit 100 loop
      t_name = r.tablename; 
      update  t_name set id = 10 where id = 15; 
  end loop; 
  return seq_name;
end;
$$
language plpgsql;

shows ERROR: relation "t_name" does not exist

+4
source share
1 answer

The correct answer is a comment by Anton Kovalenko

You cannot use a variable as the name of a table or column in embedded SQL.

UPDATE dynamic_table_name SET ....

PostgreSQL SQL, () - - , . , PostgreSQL ( - ).

SQL - . SQL

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT table_name 
              FROM information_schema.tables
             WHERE table_catalog = 'public'
  LOOP
    EXECUTE format('UPDATE %I SET id = 10 WHERE id = 15', r.table_name);
  END LOOP;
END $$;

: SQL ( SQL-) . "format". quote_ident.

EXECUTE 'UPDATE ' || quote_ident(r.table_name) || 'SET ...
+4

More articles:


All Articles