I've got a view, and I've got a function that returns records out of this view. This is actually the view definition:
CREATE VIEW ctags(id, name, descr, freq) AS SELECT tags.conc_id, expressions.name, concepts.descr, tags.freq FROM tags, concepts, expressions WHERE concepts.id = tags.conc_id AND expressions.id = concepts.expr_id;
The column id references towards the table tags, that, references to a different table concepts, which, consequently, references towards the table expressions. Listed here are the table definitions:
CREATE TABLE expressions( id serial PRIMARY KEY, name text, is_dropped bool DEFAULT FALSE, rank float(53) DEFAULT 0, state text DEFAULT 'never edited', UNIQUE(name) ); CREATE TABLE concepts( id serial PRIMARY KEY, expr_id int NOT NULL, descr text NOT NULL, source_id int, equiv_p_id int, equiv_r_id int, equiv_len int, weight int, is_dropped bool DEFAULT FALSE, FOREIGN KEY(expr_id) REFERENCES expressions, FOREIGN KEY(source_id), FOREIGN KEY(equiv_p_id) REFERENCES concepts, FOREIGN KEY(equiv_r_id) REFERENCES concepts, UNIQUE(id,equiv_p_id), UNIQUE(id,equiv_r_id) ); CREATE TABLE tags( conc_id int NOT NULL, freq int NOT NULL default 0, UNIQUE(conc_id, freq) );
The table expressions can also be recommended from the view (ctags). I would like my function to mix rows of my view, which have equal values within the column title which make reference to rows on the table concepts with equal values from the column equiv_r_id to ensure that these rows are combined only one time, the combined row has one (does not matter which) from the ids, the need for the column descr is concatenated in the values from the rows being combined, and also the row freq consists of the sum values in the rows being combined. I've no clue how to get it done, any help could be appreciated.
Essentially, that which you describe appears like this:
CREATE FUNCTION f_test() RETURNS TABLE(min_id int, name text, all_descr text, sum_freq int) AS $x$ SELECT min(t.conc_id) -- AS min_id ,e.name ,string_agg(c.descr, ', ') -- AS all_descr ,sum(t.freq) -- AS sum_freq FROM tags t JOIN concepts c USING (id) JOIN expressions e ON e.id = c.expr_id; -- WHERE e.name IS DISTINCT FROM $x$ LANGUAGE sql;
- I overlooked the vista ctags altogether as that's not necessary.
- You might write this as View to date, the function wrapper is not required.
You'll need PostgreSQL 9.+ for [cde]. Else you need to substitute with
array_to_string(array_agg(c.descr), ', ')
The only real unclear part is:
which make reference to rows on the table concepts with equal values from the column equiv_r_id to ensure that these rows are combined only one time
Waht column exactly describes what column in table
concepts equals just what?
If you're able to clarify that part, I would have the ability to incorporate it in to the solution.