Re: It it possible to get this result in one query? - Mailing list pgsql-general

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