Syntax error in PL/pgSQL FOR loop

Question

I'm trying to use a FOR loop:

create or replace function update_revisions() returns trigger as 
$$ 
begin 
    declare col_name declare col_name information_schema.columns%ROWTYPE; 

    for col_name in
       select column_name from information_schema.columns
       where table_name='t'
    loop 
        insert into debug_table values (col_name); 
    end loop;
end;
$$
language plpgsql;

But it always says:

syntax error at or near 'for'

Could someone please give me a hint what's wrong with it?

Declare section must be placed before begin keyword, not after, see general syntax here: postgresql.org/docs/9.3/static/plpgsql-structure.html and a few examples here: postgresql.org/docs/9.3/static/plpgsql-declarations.html — krokodilko
– krokodilko, Commented Apr 2, 2014 at 17:53
Why are you using a loop at all? The INSERT INTO ... SELECT ... form of INSERT should be sufficient. — mu is too short
– mu is too short, Commented Apr 2, 2014 at 17:54

Community · Accepted Answer · 2017-05-23 10:26:03Z

3

Immediate problem

Invalid syntax. Untangled:

create or replace function update_revisions()
  returns trigger as 
$$ 
declare
   col_name information_schema.columns%ROWTYPE; 
begin 
   for col_name in
      select column_name from information_schema.columns
      where table_name='t'
   loop 
      insert into debug_table values (col_name); 
    end loop;
end;
$$  language plpgsql;

More problems

Table names are not unique in a Postgres database. More in this recent answer:
Behaviour of NOT LIKE with NULL values
The whole approach is inefficient. Use a single INSERT statement instead:

INSERT INTO debug_table (target_column)  -- with column definition list!
SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 't'
AND    table_schema = 'public';          -- your schema

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Apr 2, 2014 at 19:16

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

BadIdeaException Over a year ago

Thanks, it works now. I'll look into the non-unique table names. The insert statement is just there for debugging purposes, not the actual production code.

Collectives™ on Stack Overflow

Syntax error in PL/pgSQL FOR loop

1 Answer 1

Immediate problem

More problems

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Immediate problem

More problems

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related