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;

Answer 1

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.

Answer 2

Hi

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;

Regards

Peter

Answer 3

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.

Answer 4

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.

Cheers

Peter

Answer 5

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.

Answer 6

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();