Can't run "for loop" in PostgreSQL

Question

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

Could you please translate "переменная цикла по кортежам должна быть переменной типа запись или списком скалярных переменных" to English? — user330315
– user330315, Commented Apr 14, 2022 at 7:04
It seems you want to get the row count for a list of tables. See here for several solutions to that (including plain SQL) — user330315
– user330315, Commented Apr 14, 2022 at 7: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
– Alex, Commented Apr 14, 2022 at 7: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
– Gena Yakovlev, Commented Apr 14, 2022 at 7:39
Because it is an embedded command for c and not psql postgresql.org/docs/14/ecpg-sql-commands.html You don't need immediate command in your case — Alex
– Alex, Commented Apr 14, 2022 at 7:52

Alex · Accepted Answer · 2022-04-14 07:23:27Z

1

Just declare r as record:

declare
   v_count integer;
   r record;
begin

answered Apr 14, 2022 at 7:23

Alex

8669 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Can't run "for loop" in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related