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-...@xxxxxxxxxxxxx (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...@xxxxxxxxx> 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...@xxxxxxxxxxxxxx) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
