How to plpgsql scripting

Поиск
Список
Период
Сортировка
От Ekaterina Amez
Тема How to plpgsql scripting
Дата
Msg-id d556ead9-643c-92c6-e7a4-f335f6da0af5@zunibal.com
обсуждение исходный текст
Ответы Re: How to plpgsql scripting
Re: How to plpgsql scripting
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Jimmy Angelakos
Дата:
Сообщение: Re: PostgreSQL10.x client Vs. PostgreSQL 11.x server
Следующее
От: J2eeInside J2eeInside
Дата:
Сообщение: Replacing Apache Solr with Postgre Full Text Search?