Обсуждение: Re: It it possible to get this result in one query?
I guess I should mention that im basically searching for a way to recusively coalesce the title. So I want to search the second table and table_one (id,title) 1 | new one table_two (id,title) 2 | new two table_three (id,title) 1 | one 2 | two 3 | three Id like an sql statement that returns... 1 | new one | [table_one,table_three] 2 | new two | [table_two,table_three] 3 | three | [table_three] On Oct 14, 4:49 pm, Nick <nboutel...@gmail.com> wrote: > Is it possible to get the results of this snip of a function without > using a function? All tables include an id and title column. > > tables := ARRAY[table_one,table_two,table_three]::VARCHAR; > CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types > VARCHAR[]); > FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP > FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP > IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN > UPDATE final_results SET r_types = > array_append(r_types,tables[t]) WHERE id = r.id; > ELSE > INSERT INTO final_results (id,title,r_types) VALUES > (r.id,r.title,ARRAY[tables.t]); > END LOOP; > END LOOP;
Found a solution for what I need. Please let me know if you know of something better/faster. -Nick CREATE AGGREGATE array_accum (anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' ); SELECT id, title, array_accum(t) AS ts FROM ( SELECT 'table_one' AS t, id, title FROM table_one UNION ALL SELECT 'table_two' AS t, b.id, COALESCE(a.title,b.title,c.title) AS title FROM table_two b LEFT JOIN table_one a ON a.id = b.id LEFT JOIN table_three c ON c.id = b.id UNION ALL SELECT 'table_three' AS t, c.id, COALESCE(a.title,b.title,c.title) AS title FROM table_three c LEFT JOIN table_one a ON a.id = c.id LEFT JOIN table_two b ON b.id = c.id ) x GROUP BY id, title; On Oct 14, 5:13 pm, Nick <nboutel...@gmail.com> wrote: > I guess I should mention that im basically searching for a way to > recusively coalesce the title. So I want to search the second table > and > > table_one (id,title) > 1 | new one > > table_two (id,title) > 2 | new two > > table_three (id,title) > 1 | one > 2 | two > 3 | three > > Id like an sql statement that returns... > 1 | new one | [table_one,table_three] > 2 | new two | [table_two,table_three] > 3 | three | [table_three] > > On Oct 14, 4:49 pm, Nick <nboutel...@gmail.com> wrote: > > > > > Is it possible to get the results of this snip of a function without > > using a function? All tables include an id and title column. > > > tables := ARRAY[table_one,table_two,table_three]::VARCHAR; > > CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types > > VARCHAR[]); > > FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP > > FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP > > IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN > > UPDATE final_results SET r_types = > > array_append(r_types,tables[t]) WHERE id = r.id; > > ELSE > > INSERT INTO final_results (id,title,r_types) VALUES > > (r.id,r.title,ARRAY[tables.t]); > > END LOOP; > > END LOOP;- Hide quoted text - > > - Show quoted text -
Sure: select t3.id, coalesce ( t1.title, t2.title, t3.title ), coalesce ( case when t1.title is not null then 'table_one,' else null end, case when t2.title is not null then 'table_two,' else null end, '' ) || 'table_three' from table_three t3 left outer join table_two t2 using (id) left outer join table_one t1 using (id) On 10/14/2010 8:13 PM, Nick wrote: > I guess I should mention that im basically searching for a way to > recusively coalesce the title. So I want to search the second table > and > > table_one (id,title) > 1 | new one > > table_two (id,title) > 2 | new two > > table_three (id,title) > 1 | one > 2 | two > 3 | three > > Id like an sql statement that returns... > 1 | new one | [table_one,table_three] > 2 | new two | [table_two,table_three] > 3 | three | [table_three] > > > On Oct 14, 4:49 pm, Nick<nboutel...@gmail.com> wrote: >> Is it possible to get the results of this snip of a function without >> using a function? All tables include an id and title column. >> >> tables := ARRAY[table_one,table_two,table_three]::VARCHAR; >> CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types >> VARCHAR[]); >> FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP >> FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP >> IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN >> UPDATE final_results SET r_types = >> array_append(r_types,tables[t]) WHERE id = r.id; >> ELSE >> INSERT INTO final_results (id,title,r_types) VALUES >> (r.id,r.title,ARRAY[tables.t]); >> END LOOP; >> END LOOP; > > -- Guy Rouillier
Thanks Guy, is it possible to get the 3rd column result as an array instead of string? -Nick On Oct 14, 9:27 pm, guyr-...@burntmail.com (Guy Rouillier) wrote: > Sure: > > select > t3.id, > coalesce > ( > t1.title, > t2.title, > t3.title > ), > coalesce > ( > case > when t1.title is not null > then 'table_one,' > else null > end, > case > when t2.title is not null > then 'table_two,' > else null > end, > '' > ) || 'table_three' > from > table_three t3 > left outer join table_two t2 using (id) > left outer join table_one t1 using (id) > > On 10/14/2010 8:13 PM, Nick wrote: > > > > > > > I guess I should mention that im basically searching for a way to > > recusively coalesce the title. So I want to search the second table > > and > > > table_one (id,title) > > 1 | new one > > > table_two (id,title) > > 2 | new two > > > table_three (id,title) > > 1 | one > > 2 | two > > 3 | three > > > Id like an sql statement that returns... > > 1 | new one | [table_one,table_three] > > 2 | new two | [table_two,table_three] > > 3 | three | [table_three] > > > On Oct 14, 4:49 pm, Nick<nboutel...@gmail.com> wrote: > >> Is it possible to get the results of this snip of a function without > >> using a function? All tables include an id and title column. > > >> tables := ARRAY[table_one,table_two,table_three]::VARCHAR; > >> CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types > >> VARCHAR[]); > >> FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP > >> FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP > >> IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN > >> UPDATE final_results SET r_types = > >> array_append(r_types,tables[t]) WHERE id = r.id; > >> ELSE > >> INSERT INTO final_results (id,title,r_types) VALUES > >> (r.id,r.title,ARRAY[tables.t]); > >> END LOOP; > >> END LOOP; > > -- > Guy Rouillier > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
On Fri, Oct 15, 2010 at 2:55 AM, Nick <nboutelier@gmail.com> wrote: > Thanks Guy, is it possible to get the 3rd column result as an array > instead of string? -Nick tbh, your solution using array_agg over union all upthread looked spot on... merlin
Sure, did you look in the documentation? select t3.id, coalesce ( t1.title, t2.title, t3.title ), string_to_array(coalesce ( case when t1.title is not null then 'table_one,' else null end, case when t2.title is not null then 'table_two,' else null end, '' ) || 'table_three', ',') from table_three t3 left outer join table_two t2 using (id) left outer join table_one t1 using (id) On 10/15/2010 2:55 AM, Nick wrote: > Thanks Guy, is it possible to get the 3rd column result as an array > instead of string? -Nick > > On Oct 14, 9:27 pm, guyr-...@burntmail.com (Guy Rouillier) wrote: >> Sure: >> >> select >> t3.id, >> coalesce >> ( >> t1.title, >> t2.title, >> t3.title >> ), >> coalesce >> ( >> case >> when t1.title is not null >> then 'table_one,' >> else null >> end, >> case >> when t2.title is not null >> then 'table_two,' >> else null >> end, >> '' >> ) || 'table_three' >> from >> table_three t3 >> left outer join table_two t2 using (id) >> left outer join table_one t1 using (id) >> >> On 10/14/2010 8:13 PM, Nick wrote: >> >> >> >> >> >>> I guess I should mention that im basically searching for a way to >>> recusively coalesce the title. So I want to search the second table >>> and >> >>> table_one (id,title) >>> 1 | new one >> >>> table_two (id,title) >>> 2 | new two >> >>> table_three (id,title) >>> 1 | one >>> 2 | two >>> 3 | three >> >>> Id like an sql statement that returns... >>> 1 | new one | [table_one,table_three] >>> 2 | new two | [table_two,table_three] >>> 3 | three | [table_three] >> >>> On Oct 14, 4:49 pm, Nick<nboutel...@gmail.com> wrote: >>>> Is it possible to get the results of this snip of a function without >>>> using a function? All tables include an id and title column. >> >>>> tables := ARRAY[table_one,table_two,table_three]::VARCHAR; >>>> CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types >>>> VARCHAR[]); >>>> FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP >>>> FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP >>>> IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN >>>> UPDATE final_results SET r_types = >>>> array_append(r_types,tables[t]) WHERE id = r.id; >>>> ELSE >>>> INSERT INTO final_results (id,title,r_types) VALUES >>>> (r.id,r.title,ARRAY[tables.t]); >>>> END LOOP; >>>> END LOOP; >> >> -- >> Guy Rouillier >> >> -- >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general > > -- Guy Rouillier