How do I run / execute PL/pgSQL?

Question

This might seem like a silly question, but how do I "run" PL/pgSQL code?

I tried using psql however I'm getting:

postgres=# exampleTest varchar := 'hello';
ERROR:  syntax error at or near "exampleTest"
LINE 1: exampleTest varchar := 'hello';

I'm trying to make it easier to run/analyze some queries (which I'm using pgcli for right now), I could do some shell scripts for this but that will likely be a bit cumbersome with string handing, in addition using pgcli I have useful autocomplete which I don't have in my text editor which I would use for the shell scripts.

An example of a usecase:

I have a main query X that I run to see the results. I then want to count the number of records returned from query X, to do that I surround the whole thing in SELECT count(*) from ( X ) main_query. But now each time I want to change the X query and run both queries at the same time I have to manipulate the cli prompt.

Ideally I could just set the query X to a variable and then just update it, and then just hit 'up' arrow to rerun a previous prompt / query like SELECT COUNT(*) FROM X.

Answer 1

You don't need PL/pgSQL for the use case you describe:

postgres=# \set foo 'select ''bar'''
postgres=# :foo;
 ?column?
----------
 bar
(1 row)

postgres=# select count(*) from (:foo);
 count
-------
     1
(1 row)