Re: It it possible to get this result in one query?

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