From: | Nick <nboutelier(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: It it possible to get this result in one query? |
Date: | 2010-10-15 01:05:38 |
Message-ID: | 30ccb6ab-44e8-4447-803f-ddec930e0470@a7g2000prb.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)(dot)(dot)(at)gmail(dot)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(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;- Hide quoted text -
>
> - Show quoted text -
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2010-10-15 01:29:54 | Re: how to get current sql execution time? |
Previous Message | sunpeng | 2010-10-15 00:18:16 | Re: how to write an optimized sql with two same subsql? |