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.

  • 1
    PL/pgSQL needs a DO block or a function/procedure. You can't use PL/pgSQL as part of regular SQL
    – user1822
    Commented Jan 27, 2023 at 14:06
  • But psql supports variables
    – user1822
    Commented Jan 27, 2023 at 14:07

1 Answer 1


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

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

postgres=# select count(*) from (:foo);
(1 row)
  • Seems like this would work but for psql, it's not supported on pgcli. Thanks though. Commented Jan 27, 2023 at 14:29
  • 1
    But you mentioned psql yourself?
    – mustaccio
    Commented Jan 27, 2023 at 14:42
  • Just for demonstration purposes to figure out if plpgsql can be run 'directly'. Commented Jan 27, 2023 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.