It it possible to get this result in one query?

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]

Found a solution for what I need. Please let me know if you know of
something better/faster. -Nick

CREATE AGGREGATE array_accum (anyelement) (
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
SELECT id, title, array_accum(t) AS ts FROM (
SELECT 'table_one' AS t, id, title FROM table_one
UNION ALL
SELECT 'table_two' AS t, b.id, COALESCE(a.title,b.title,c.title) AS
title FROM table_two b
LEFT JOIN table_one a ON a.id = b.id
LEFT JOIN table_three c ON c.id = b.id
UNION ALL
SELECT 'table_three' AS t, c.id, COALESCE(a.title,b.title,c.title)
AS title FROM table_three c
LEFT JOIN table_one a ON a.id = c.id
LEFT JOIN table_two b ON b.id = c.id
) x GROUP BY id, title;

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

Thanks Guy, is it possible to get the 3rd column result as an array
instead of string? -Nick

On Fri, Oct 15, 2010 at 2:55 AM, Nick <nboutelier@gmail.com> wrote:

Thanks Guy, is it possible to get the 3rd column result as an array
instead of string? -Nick

tbh, your solution using array_agg over union all upthread looked spot on...

merlin

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