Declare Variable Not In Function by postgres

postgresql variables declare
Nike picture Nike · Mar 28, 2011 · Viewed 10.4k times · Source

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

a_horse_with_no_name picture a_horse_with_no_name · Mar 28, 2011

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

Related questions

Declare variable set = select

This probably sounds like a really stupid question, but how do I declare a variable for used in a PostgreSQL 9.3 query? CREATE or replace FUNCTION public.test() returns int4 AS $BODY$ DECLARE cod_process bigint :=30001; cod_instance bigint ; utc_log …

Read More
How do you use variables in a simple PostgreSQL script?

For example, in MS-SQL, you can open up a query window and run the following: DECLARE @List AS VARCHAR(8) SELECT @List = 'foobar' SELECT * FROM dbo.PubLists WHERE Name = @List How is this done in PostgreSQL? Can it be done?

Read More
How do you use script variables in psql?

In MS SQL Server, I create my scripts to use customizable variables: DECLARE @somevariable int SELECT @somevariable = -1 INSERT INTO foo VALUES ( @somevariable ) I'll then change the value of @somevariable at runtime, depending on the value that I want in …

Read More