I did not fully understand how to do the following: I have a sign with the object "book". Each book is marked with several sections (tags, as on hashcode.ru). Sections are stored on a single line, separated by spaces. I need to make a request in which a given author will display sections and their number. As I understand it, you should do something like this:

  1. We make a request for a table of books for a match by the name of the author, in which we select all sections of the books. Approximately it looks like this: SELECT string_to_array(tags, " " FROM book WHERE author = 'Иван Иванов'; , then pass the result to tags_chart .
  2. Then we implement the f- tags_chart . This will be the usual pl / pgsql function.

You can write it like this:

 CREATE OR REPLACE FUCNTION tags_chart(SETOF character)) RETURNS SETOF record AS '...' LANGUAGE pl/pgsql;

The question is how to implement a mechanism for counting the number of elements. Solution in the forehead to create a record, label - the number, and walk through all the elements, while finding the label in the records to increase the number. But as it seems to me, this is not true. Here the complexity is O (n ^ 2). With the condition that I need to choose not everything, but only the first n marks of the author, the feeling that the task should be solved differently.

  • one
    Do not reinvent the wheel - keep the sections not in a row, but in the service table - timka_s

1 answer 1

I would do it a little differently (by the way, I would like to know if it’s done better or worse)

First, we write the conversion procedure (embossed from the Internet):

 create or replace function unnest(anyarray) returns setof anyelement language sql as $$ select $1[i] from generate_series(array_lower($1, 1), array_upper($1, 1)) as i; $$;

Then, using the subquery, we get the necessary data:

 SELECT count(*) as num, name FROM (SELECT unnest(string_to_array(tags, ' ')) AS name FROM book WHERE author = 'Иван Иванов') tag GROUP BY name ORDER BY num DESC LIMIT 10;