How invoke PL/pgSQL from command line instead of SQL

Question

I write PL/SQL scripts using Oracle and I'm trying my first PL/pgSQL script with PostgreSQL. For example, I created this test.sql file.

DECLARE
    v_loc_nbr         INTEGER;
BEGIN
    v_loc_nbr := 0;
END;

Which I try to execute using the command line:

\postgresql\9.3\bin\psql.exe -d postgres -f test.sql

but I get syntax errors like:

psql:test.sql:4: ERROR: syntax error at or near "v_loc_nbr"

I think the problem is it trying to execute SQL when I want it to run PL/pgSQL. What should the command be?

postgresql.org/docs/current/static/sql-do.html

user330315
– user330315

2014-12-05 23:02:34 +00:00
Commented Dec 5, 2014 at 23:02 — user330315
– user330315, Commented Dec 5, 2014 at 23:02

Erwin Brandstetter · Accepted Answer · 2025-04-03 22:10:10Z

4

Like with other procedural languages, you cannot run PL/pgSQL directly.
You can only run SQL code. Use a PL/pgSQL code block inside a function body or wrapped in a DO command. The latter does not take or return data.

Check out the plpgsql tag for examples.

edited Apr 3 at 22:10

answered Dec 5, 2014 at 23:20

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:05:37Z

3

I don't want to explain more about this because Erwin explained well.You need to wrap your sql inside a DO, so your test.sql should write like this

DO
$$
DECLARE
    v_loc_nbr INTEGER;
BEGIN
    v_loc_nbr := 0;
END;
$$

and try to execute it \postgresql\9.3\bin\psql.exe -d postgres -f test.sql

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Dec 6, 2014 at 4:56

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

How invoke PL/pgSQL from command line instead of SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related