How to plpgsql scripting

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

How to plpgsql scripting

Ekaterina Amez

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


Reply | Threaded
Open this post in threaded view
|

Re: How to plpgsql scripting

Pavel Stehule


st 25. 3. 2020 v 13:20 odesílatel Ekaterina Amez <[hidden email]> 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


Kind regards,

Ekaterina


Reply | Threaded
Open this post in threaded view
|

Re: How to plpgsql scripting

David G Johnston
In reply to this post by Ekaterina Amez
On Wednesday, March 25, 2020, Ekaterina Amez <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: How to plpgsql scripting

Ekaterina Amez
In reply to this post by Pavel Stehule


El mié., 25 mar. 2020 a las 13:42, Pavel Stehule (<[hidden email]>) 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.


Regards

Pavel


Kind regards,

Ekaterina


Reply | Threaded
Open this post in threaded view
|

Re: How to plpgsql scripting

Ekaterina Amez
In reply to this post by David G Johnston

El mié., 25 mar. 2020 a las 13:54, David G. Johnston (<[hidden email]>) escribió:
On Wednesday, March 25, 2020, Ekaterina Amez <[hidden email]> 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.


David J.