How to plpgsql scripting

From: Ekaterina Amez <ekaterina(dot)amez(at)zunibal(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to plpgsql scripting
Date: 2020-03-25 12:20:23
Message-ID: d556ead9-643c-92c6-e7a4-f335f6da0af5@zunibal.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message J2eeInside J2eeInside 2020-03-25 12:36:38 Replacing Apache Solr with Postgre Full Text Search?
Previous Message Jimmy Angelakos 2020-03-25 10:28:03 Re: PostgreSQL10.x client Vs. PostgreSQL 11.x server