2

I'm wondering how to pop a value off from the top of an Array in postgresql using plpgsql?

CREATE FUNCTION pop_off(arr ANYARRAY) RETURNING ANYARRAY AS $$
    BEGIN
        -- return array with first element removed
    END;
$$ LANGUAGE plpgsql;
obimod
  • 797
  • 10
  • 26

1 Answers1

1

Well, this wasn't as tough as I thought. Let's leverage the array_length method...

CREATE OR REPLACE FUNCTION pop_off(arr ANYARRAY) RETURNS ANYARRAY AS $$
    BEGIN
        RETURN (SELECT arr[2:array_length(arr,1)]);
    END;
$$ LANGUAGE plpgsql;

There we go! Now let's test it...

LOG:  statement: CREATE OR REPLACE FUNCTION pop_off(arr ANYARRAY) RETURNS ANYARRAY AS $$
    BEGIN
        RETURN (SELECT arr[2:array_length(arr,1)]);
    END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
test_database=# SELECT pop_off('{1,2,3}'::int[]);
LOG:  statement: SELECT pop_off('{1,2,3}'::int[]);
 pop_off 
--------- 
{2,3}
(1 row)
obimod
  • 797
  • 10
  • 26
  • 1
    This will *work* but performance will be awful. You're not modifying the array in place, you're allocating a new array each time. Given the chance I tend to do this sort of thing in C, pass the array and an offset into it to a function, or otherwise avoid the need to re-construct arrays over and over. – Craig Ringer Jul 30 '13 at 10:31
  • Excellent suggestion. Seems like a simple enough C-function.. could we trouble you for an answer post? It has been a long time since I've successfully ran into pointer notation :) – obimod Jul 30 '13 at 13:48
  • Aah, you haven't had the pleasure of working with PostgreSQL's array API yet? I do Stack Overflow for fun and learning, and working with Pg's arrays in C fails part 1 pretty badly ;-) . I've been working on an `array_find` function for core and that's been enough C API array work for a while. It's quite reasonable to pass arrays around in PL/PgSQL along with a "current index" value, as the array is passed by reference not value, so you don't *need* to do it in C. It's only when you start actually modifying the array as you are doing that performance in PL/PgSQL becomes awful. – Craig Ringer Jul 31 '13 at 02:25
  • Eek... sounds like I should restructure out the need for arrays.. thanks, Craig. If you like horror flicks you might smile at my last unsuccessful attempt with a C-function in pg.. http://stackoverflow.com/questions/14805026/rrule-parsing-in-postgresql – obimod Jul 31 '13 at 04:01
  • You can use pure SQL directly, and `IMMUTABLE`... To little bit better performance. `CREATE FUNCTION pop_off(ANYARRAY) RETURNS ANYARRAY AS $$ SELECT $1[2:array_length($1,1)]; $$ LANGUAGE sql IMMUTABLE;` – Peter Krauss Jul 20 '17 at 19:13