variable declaration line in .sql file failing with syntax error

Question

I have the following code in a .sql file that I run against my postgresql database:

INSERT INTO widget (username)
SELECT i
FROM generate_series(100, 150) AS t(i);

INSERT INTO widget2
SELECT generate_series(100, 150), 'statictext', generate_series(100, 150);

And I run it by doing something like "psql -U myuser -d widgets -f addwidgets.sql But now I need to parameterize these items. So I've created this version instead:

DECLARE startnum INTEGER;
DECLARE endnum INTEGER;
startnum:=100;
endnum:=150;
INSERT INTO widget (username)
SELECT i
FROM generate_series(startnum, endnum) AS t(i);

INSERT INTO widget2
SELECT generate_series(startnum, endnum), 'statictext', generate_series(startnum, endnum);

But it bombs because it doesn't like my startnum / endnum variables. The error message is:

psql:addwidgets.sql:1: ERROR:  syntax error at or near "INTEGER"
LINE 1: DECLARE startnum INTEGER;

In searching for an answer, I found that it could be related to the language I'm using in psql. Right now i'm googling how to find out what scripting language my database engine is set up to understand. But in the mean time if you have any suggestions I'd appreciate it.

EDIT 1

So I found this:

testbox:/tmp# psql -U postgres -d widgets -c "select * from pg_language;"
 lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
----------+----------+---------+--------------+---------------+-----------+--------------+--------
 internal |       10 | f       | f            |             0 |         0 |         2246 | 
 c        |       10 | f       | f            |             0 |         0 |         2247 | 
 sql      |       10 | f       | t            |             0 |         0 |         2248 | 
 plpgsql  |       10 | t       | t            |         11864 |     11865 |        11866 |

Perhaps I should specify at the top of my .sql file what language I'm using? it would also help me to know which documentation to reference I guess. :)

I don't think you can just write raw PL/PGSQL, you can create functions with it though. — Sami Kuhmonen
– Sami Kuhmonen, Commented Dec 23, 2016 at 18:25
function as in stored proc? i don't think that's what i want to do... unless of course i have to. this script will run as a part of a database initialization process and seed it with some required data. — Happydevdays
– Happydevdays, Commented Dec 23, 2016 at 18:32
you can't mix PL/pgSQL with plain SQL. You at least need to put the PL/pgSQL code into a DO block: postgresql.org/docs/current/static/sql-do.html — user330315
– user330315, Commented Dec 23, 2016 at 18:36

krokodilko · Accepted Answer · 2016-12-23 20:32:59Z

1

Use anonymous block: https://www.postgresql.org/docs/9.6/static/sql-do.html

DO [ LANGUAGE lang_name ] code

Example:

DO $$
DECLARE startnum INTEGER;
        endnum INTEGER;
BEGIN
   startnum:=100;
   endnum:=150;

   INSERT INTO widget (username)
   SELECT i
   FROM generate_series(startnum, endnum) AS t(i);

   INSERT INTO widget2
   SELECT generate_series(startnum, endnum), 'statictext', 
          generate_series(startnum, endnum);
END $$
;

answered Dec 23, 2016 at 20:32

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

variable declaration line in .sql file failing with syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related