How to use variables in a raw postgresql script block, outside of a function

So there are times that you need to run a raw postgres script that contains variables, on a simple sql script execution window — for example in pgadmin script window.

You may have come across the problem that you cannot just declare your variables in a place somewhere in your postgres script and start using them as you would do for example in Microsoft SQL (perhaps with Management Studio), without having to declare the variables within the context of a postrges function, or some kind of code block...

This can be resolved if you use what is called an anonymous block.

I find the anonymous block very useful when I need to quickly isolate parts of bigger postgres functions in order to investigate them further and do some quick troubleshoothing…

Here’s an example of an anonymous block that contains a couple of variables and a few lines of code:

DO $$
DECLARE v_studentname text;
DECLARE v_studentid bigint;
BEGIN 
  v_studentname := 'Alex Papadatos';
  INSERT INTO student (fullname) VALUES (v_studentname) 
  RETURNING id INTO lastid;

  SELECT * FROM student WHERE id = v_studentid;
END $$

Just copy it in the the ‘Query Tool’ and you are good to execute it without any errors.