Re: How to plpgsql scripting

st 25. 3. 2020 v 13:20 odesílatel Ekaterina Amez <ekaterina(dot)amez(at)zunibal(dot)com>
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
>
>
>