PostgreSql - Declare integer variable

Question

I'm trying to declare integer variable with default value 0 in postgresql sql script:

DECLARE user_id integer;

but it returns exception:

ERROR: syntax error at or near "integer"

I'm not sure how can I declare a variable and then use this variable inside while loop.

Answer 1

You must put your code inside a user defined function. It will not work on a sql window. Example below is a function that returns the number you send.the variable

  --mybase is your database name. If public, remove it.
  CREATE OR REPLACE FUNCTION mybase.my_new_rotine(numeric)
  RETURNS numeric AS
  $BODY$
  --here, get the first variable from function
    declare id numeric= $1;

    begin
   --return the number
       return id;
    end;
  $BODY$

  LANGUAGE plpgsql VOLATILE

An then, you can use it on a sql window, like this:

  select * from mybase.my_new_rotine(1)

will return 1

Answer 2

You can't use DECLARE in a SQL statement. What you want to do requires plpgsql. danielarend's answer is right, but you might want to explore DO (https://www.postgresql.org/docs/current/static/sql-do.html) which lets you write adhoc plpgsql without the need of defining a function.