0

I'm trying to create temp table and insert some data from LOOP. I need to count all strings on each table, but i have error like this:

ERROR: ОШИБКА:  переменная цикла по кортежам должна быть переменной типа запись или списком скалярных переменных
LINE 16: for r in (select "table_name", "name_campaign" from "Table_7...
             ^


SQL state: 42601
Character: 206

Table names all in one existing table.

 do
$body$

declare

   v_count integer;
    
begin

set enable_seqscan = off;

drop table if exists tmp_statyks;

create temp table tmp_statyks(table_name varchar(60), name varchar(60), x_count int);

for r in (select "table_name", "name_campaign" from "Table_7517829240") 
    loop
        execute immediate 'select count(*) from ' || r."table_name" 
            into v_count;
        INSERT INTO tmp_statyks("table_name", "name" , "x_count")
        VALUES (r."table_name",r."name_campaign",v_count);
    end loop;

end
$body$
language 'plpgsql';
select * from tmp_statyks
Gena Yakovlev
  • 1
  • 1
  • 1
    Could you please translate "переменная цикла по кортежам должна быть переменной типа запись или списком скалярных переменных" to English? –  Apr 14 '22 at 07:04
  • It seems you want to get the row count for a list of tables. See [here](https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres) for several solutions to that (including plain SQL) –  Apr 14 '22 at 07:08
  • @a_horse_with_no_name An error translation is: `ERROR: loop variable of loop over rows must be a record variable or list of scalar variables` – Alex Apr 14 '22 at 07:28
  • @Alex big thanks to you! I have troubles with russian error translates (:. And now I have error like: ERROR: ОШИБКА: тип "immediate" не существует LINE 1: SELECT immediate 'select count(*) from ' || r."table_name" ^ QUERY: SELECT immediate 'select count(*) from ' || r."table_name" CONTEXT: функция PL/pgSQL inline_code_block, строка 18, оператор EXECUTE – Gena Yakovlev Apr 14 '22 at 07:39
  • Because it is an embedded command for `c` and not psql https://www.postgresql.org/docs/14/ecpg-sql-commands.html You don't need `immediate` command in your case – Alex Apr 14 '22 at 07:52

1 Answers1

1

Just declare r as record:

declare
   v_count integer;
   r record;
begin
Alex
  • 706
  • 7
  • 16