Thread: How to plpgsql scripting

Hi List,

I'm used to make my own scripts in Oracle plsql, Sql Server tsql... but I'm unable to make one simple script in Postgres.

Objective version is 8.4 (I know, I know... it's a legacy server, I'm planning upgrade this server as soon as I can).

I have a test server with 9.2 version where I've succesfully run this code from psql:

DO $$
DECLARE 
   a integer := 10; 
   b integer := 20; 
   c integer; 
BEGIN 
   c := a + b;
    RAISE NOTICE'Value of c: %', c;
END $$;

But this syntax is (anonymous code block?) is available since 9.0 so I'm trying to adapt this to v8.4

A per documentation [https://www.postgresql.org/docs/8.4/plpgsql-structure.html], the structure of a code block is defined as:

[ <<label>> ]
[ DECLARE   declarations ]
BEGIN   statements
END [ label ];

so I've adapted my code to:

DECLARE 
   a integer;
   b integer;
   c integer;
BEGIN 
    a := 10;
    b := 20;
    c := a + b;
    RAISE NOTICE'Value of c: %', c;
END ;

But when I run this from psql, both versions 8.4 and 9.2, all I get is:

testdb=# DECLARE 
testdb-#    a integer;
ERROR:  syntax error at or near «integer»
LINE 2:    a integer;
              ^
testdb=#    b integer;
ERROR:  syntax error at or near «b»
LINE 1: b integer;
         ^
testdb=#    c integer;
ERROR:  syntax error at or near «c»
LINE 1: c integer;
         ^
testdb=# BEGIN 
testdb-# a := 10;
ERROR:  syntax error at or near «a»
LINE 2: a := 10;
         ^
testdb=# b := 20;
ERROR:  syntax error at or near «b»
LINE 1: b := 20;
         ^
testdb=#    c := a + b;
ERROR:  syntax error at or near «c»
LINE 1: c := a + b;
         ^
testdb=#     RAISE NOTICE'Value of c: %', c;
ERROR:  syntax error at or near «RAISE»
LINE 1: RAISE NOTICE'Value of c: %', c;
         ^
testdb=# END;
WARNING:  no hay una transacción en curso
COMMIT
testdb=#

NOTE: I've translated error messages myself.

What's wrong with the syntax? Or is not possible to make a script and I have to create a function to encapsulate my code?

Kind regards,

Ekaterina

just this is not supported feature.

You have some special reason why you use 8.4? It's pretty old unsupported version.

Regards

Pavel

Kind regards,

Ekaterina

On Wednesday, March 25, 2020, Ekaterina Amez <ekaterina.amez@zunibal.com> wrote:

What's wrong with the syntax? Or is not possible to make a script and I have to create a function to encapsulate my code?

This not working exactly the reason the “DO” command was created..... 

David J.