How to plpgsql scripting

st 25. 3. 2020 v 13:20 odesílatel Ekaterina Amez <ekaterina.amez@zunibal.com>
napsal:

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

you cannot to do this.

If you want to use plpgsql in older releases, you should to write functions
and then run these functions.

A per documentation [

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

yes, but the block must be wrapped by some SQL statement - in 8.4, by
CREATE OR REPLACE FUNCTION

[ <<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?

just this is not supported feature.

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

Regards

Pavel

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.

El mié., 25 mar. 2020 a las 13:42, Pavel Stehule (<pavel.stehule@gmail.com>)
escribió:

just this is not supported feature.

I was affraid this was going to be the answer.. sigh

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

As I said: legacy server. I'm planning upgrade it.

El mié., 25 mar. 2020 a las 13:54, David G. Johnston (<
david.g.johnston@gmail.com>) escribió:

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.....

Thank you David, I was beginning to suppose this.