using Array_append gives me syntax error when creating PostgreSQL function

Question

Here is the code

CREATE OR REPLACE FUNCTION primes (IN   integer) RETURNS TEXT AS $$
DECLARE
    counter INTEGER = $1;
    primes int [];
    mycount int;
  BEGIN
    WHILE counter != 0 LOOP
      mycount := count(primes);
      array_append(primes [counter], mycount);
      counter := counter - 1;
    END LOOP;
    RETURN array_to_text(primes[], ',');
  END;
$$
LANGUAGE 'plpgsql'

This is me developing the beginnings of a prime generating function. I am trying to simply get it to return the 'count' of the array. So if I pass '7' into the function I should get back [0, 1, 2, 3, 4, 5, 6].

But when I try to create this function I get

SQL Error: ERROR:  syntax error at or near "array_append" LINE 1: array_append( $1  [ $2 ],  $3 )
        ^ QUERY:  array_append( $1  [ $2 ],  $3 ) CONTEXT:  SQL statement in PL/PgSQL function "primes" near line 8

I am a newbie with postgres functions. I am not understanding why I cannnot get this array to work properly.

Again all I want is to just fill this array up correctly with the 'current' count of the array. (It's more to just help me understand that it is in fact doing the loop correctly and is counting it correctly).

Thank you for your help.

I presume that you're only doing this to learn PL/SQL - I think SO would be a more appropriate place for this question. — MikeyB
– MikeyB, Commented Mar 13, 2012 at 1:09

mu is too short · Accepted Answer · 2012-03-13 08:15:06Z

29

From the fine manual:

Function: array_append(anyarray, anyelement)
Return Type: anyarray
Description: append an element to the end of an array

So array_append returns an array and you need to assign that return value to something. Also, I think you want array_to_string at the end of your function, not array_to_text. And primes is an array so you want array_append(primes, mycount) rather than trying to append to an entry in primes.

CREATE OR REPLACE FUNCTION primes (IN integer) RETURNS TEXT AS $$
DECLARE
    counter INTEGER = $1; 
    primes int []; 
    mycount int; 
BEGIN
    WHILE counter != 0 LOOP 
        mycount := count(primes); 
        primes  := array_append(primes, mycount);
        counter := counter - 1; 
    END LOOP;
    RETURN array_to_string(primes, ',');   
END;   
$$ LANGUAGE 'plpgsql';

I don't know what you intend mycount := count(primes); to do, perhaps you meant to say mycount := array_length(primes, 1); so that you would get a sequence of consecutive integers in primes.

answered Mar 13, 2012 at 8:15

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

StanM Over a year ago

Awesome. Thank you that was my problem(s). And yes I was looking for array_length - I am trying to learn SQL programming syntax. It's a whole new world.

mu is too short Over a year ago

@StanQA: You might want to have a look at generate_series for producing sequences. You'd have to think in terms of sets/tables rather than loops but that's how you have to approach SQL anyway.

Collectives™ on Stack Overflow

using Array_append gives me syntax error when creating PostgreSQL function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related