Determine size of input array plpgsql

Question

I have a plpgsql function that takes an integer[] as input.

The whole setup can be found in this question: Passing a record as function argument PL/pgSQL

Short version: I have an n to m relationship from books to authors with a link table called books_author. I now have a function that looks like this:

    create function f_insert_books(title varchar, isbn varchar, publisher varchar,  
      author_id integer[]) returns void as $$
      begin
      --insert book
      --insert link to authors into the books_author table
      end;
    $$ language plpgsql;

I now want to add the number_of_authors to the book. Is there an easy way to determine the size of the author_id array or would you recomment passing the "number_of_authors int" as an input parameter?

I found this suggestion, but I am a bit worried about performance in this approach. So maybe there's something easier/faster. http://archives.postgresql.org/pgsql-sql/2000-06/msg00169.php

Thank you very much for your help.

score 12 · Accepted Answer · answered Oct 05 '12 at 08:28

Use array_length:

SELECT array_length( ARRAY[1,2,3], 1 );

The 2nd parameter is the dimension you're interested in. Most arrays are 1-dimensional, so 1 is the right value in most cases.

If you're in fact looking for the length of all the strings in a text array, use array_to_string with length:

SELECT length(array_to_string( ARRAY['a','bb','ccc'], '' ));

BTW, the article you linked to is 12 years old and appears completely obsolete.

score 1 · Answer 2 · answered May 29 '18 at 15:22

There is one more function to get the length of the array - cardinality:

SELECT cardinality(ARRAY[1,2,3]);

It works in the same way for a simple one-dimensional array and there is no need to pass any additional parameter such as array dimension in the array_length function.

Determine size of input array plpgsql

2 Answers2