Array declaration and manipulation in postgresql

Question

I would like to understand why the code below isn't working. I am using postgres 13, it is saying "syntax error near nu int[]" underlining int. I am trying to get started with array manipulation.

DECLARE
    num int[] := '{1, 2, 3, 4, 5}';
    s int := 0;
    x int;
BEGIN
    
    FOR x IN 1..5 LOOP
        s:= s + num[x];
    END LOOP;
END;

You need to use a DO block to run procedural (PL/pgSQL) code. — user330315
– user330315, Commented Apr 14, 2021 at 9:12

Jim Jones · Accepted Answer · 2021-04-14 10:55:19Z

2

Welcome to SO.

You forgot to specify in your code that you're not using SQL. To use PL/pgSQL code you either need to use a function / procedure or an anonymous code block:

DO $$ 
DECLARE
    num int[] := '{1, 2, 3, 4, 5}';
    s int := 0;
    x int;
BEGIN 
    FOR x IN 1..5 LOOP
        s:= s + num[x];
        RAISE NOTICE '%',s;  -- See the output in the console
    END LOOP;
END; $$

NOTICE:  1
NOTICE:  3
NOTICE:  6
NOTICE:  10
NOTICE:  15

answered Apr 14, 2021 at 10:55

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Array declaration and manipulation in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related