Declare Variable Not In Function by postgres

Question

I want to declare variable in postgres but not in function...

Declare
    c varchar;
    a integer;
    b integer;

    select b = count (*) from table

    set a = 1
        while a <= b  
begin 

    select c = [c] from table where id = a

    if (c = '1')
        insert into table2 select(*) from table

end


set a = a+1

but error ERROR: syntax error at or near "varchar" LINE 2: c varchar; ^ I hope anyone can help me

Answer 1

If you are on 9.0 you can use anonymous blocks using the DO statement:

http://www.postgresql.org/docs/current/static/sql-do.html

Before 9.0 you cannot use anonymous pl/pgSQL blocks, you will need to create a function for this.

Additionally you have the syntax for pl/pgSQL completely wrong.

You cannot have an assignment and a WHILE statement in the same line. You are also missing the other requird keywords for a WHILE loop.

Please refer to the manual to learn the correct syntax:

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#AEN50163

The syntax for reading a value from a select into a variable is also wrong.

The correct syntax to retrieve the result of a SELECT is:

SELECT count(*) 
  INTO b
FROM some_table;

Check out the manual for this as well:

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW