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

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-(dot)(dot)(dot)(at)burntmail(dot)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(dot)(dot)(dot)(at)gmail(dot)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(dot)(dot)(dot)(at)postgresql(dot)org)
>> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
>
>

--
Guy Rouillier