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
