How to plpgsql scripting

Поиск
Список
Период
Сортировка
Искать
От
Ekaterina Amez
Тема
How to plpgsql scripting
Дата
Msg-id
d556ead9-643c-92c6-e7a4-f335f6da0af5@zunibal.com
Список
Дерево обсуждения
How to plpgsql scripting Ekaterina Amez <ekaterina.amez@zunibal.com>
Re: How to plpgsql scripting Pavel Stehule <pavel.stehule@gmail.com>
Re: How to plpgsql scripting Ekaterina Amez <ekaterina.amez@zunibal.com>
Re: How to plpgsql scripting "David G. Johnston" <david.g.johnston@gmail.com>
Re: How to plpgsql scripting Ekaterina Amez <ekaterina.amez@zunibal.com>

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


В списке pgsql-general по дате отправления
От: Jimmy Angelakos
Дата:
От: J2eeInside J2eeInside
Дата:
FAQ