PostgreSQL Syntax error in PGAdmin

Question

I am new to PostgreSQL and am using the query tool in PGAdmin. I'm trying to run pgsql queries that use variables, but I can't seem to get the syntax right.

Here's a sample query that gives a syntax error:

DECLARE
  num INTEGER;

BEGIN

  num := 3;
  PRINT num;

END;

Update:
Ok, let me try and explain. I come from a SQL server background. In the management studio, I can open a query window and play with (T)-SQL queries.

For example, I can write something like this:

DECLARE @num INT
SET @num = 3
SELECT @num

I know this is a dumb example, but I'm just trying to declare a variable and do something with it. I'm trying to familiarise myself with PL/PGSQL.

Update, again:
It's me again. I'm trying the script below and get a "[ERROR ] 7.0-2: syntax error, unexpected character". Is this meant to work in PGAdmin?

DECLARE
  num INTEGER;

BEGIN

  num := 3;
  RAISE NOTICE '%', num;

END;

Coming from the MSSQL/TSQL world as well I ran into the same issue. I'm very surprised that you can't just write and run one-off PL/PGSQL scripts in PostgreSQL. — Tub
– Tub, Commented Nov 5, 2013 at 14:16

user1167541 · Accepted Answer · 2014-01-17 11:02:55Z

12

You can use the do statement. For example:

do $$
declare 
  num integer := 10;
begin

    RAISE INFO 'VARIABLE: %', num;

end;
$$language plpgsql;

When you use pgadmin you have to use the button EXECUTE QUERY instead of Execute pdScript, as it is explained here:

http://postgresql.1045698.n5.nabble.com/PgAmin3-Anonymous-code-block-can-t-be-executed-by-pressing-quot-Execute-PG-script-quot-button-td5771073.html

The documentation for do statements is here:

http://www.postgresql.org/docs/9.3/static/sql-do.html

answered Jan 17, 2014 at 11:02

user1167541

1211 silver badge2 bronze badges

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

Comments

filiprem · Accepted Answer · 2009-12-14 11:50:37Z

5

Just to rephrase and "concretize" what others say: There are no inline procedures in PostgreSQL. There is also no PRINT statement. You have to:

CREATE OR REPLACE FUNCTION test() RETURNS void AS $$
DECLARE
  num INTEGER;

BEGIN

  num := 3;
  RAISE NOTICE '%', num;

END;
$$ LANGUAGE plpgsql;

SELECT test();

answered Dec 14, 2009 at 11:50

filiprem

2762 silver badges9 bronze badges

Comments

Edmund · Accepted Answer · 2009-12-14 05:57:02Z

3

If you're trying to print out num (say, for debugging), you could try:

RAISE NOTICE '%', num;

http://www.postgresql.org/docs/8.4/static/plpgsql-errors-and-messages.html

PRINT doesn't mean anything in PL/pgSQL.

answered Dec 14, 2009 at 5:57

Edmund

10.8k3 gold badges42 silver badges59 bronze badges

Comments

user80168 · Accepted Answer · 2009-12-14 07:33:43Z

1

I have no idea what you are trying to achieve. PostgreSQL doesn't support this kind of syntax. Similar keywords (except PRINT?!) are in PL/pgSQL which is procedural language for building FUNCTIONS, not for writing stand-alone SQL queries.

answered Dec 14, 2009 at 7:33

user80168

Comments

Milen A. Radev · Accepted Answer · 2009-12-14 10:29:16Z

1

Postgres doesn't support anything like that by itself (yet). psql (the official command line client) has some rudimentary scripting.

The best option for you is pgAdmin which already has scripting built-in.

answered Dec 14, 2009 at 10:29

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Syntax error in PGAdmin

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related