[SOLVED] How invoke PL/pgSQL from command line instead of SQL

How invoke PL/pgSQL from command line instead of SQL

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?

Solution

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