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