Обсуждение: Re: 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] 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;
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;
On Oct 14, 5:13 pm, Nick <nboutel...@gmail.com> 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;- Hide quoted text -
>
> - Show quoted text -
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 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
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