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 00:13:57 |
Message-ID: | 4c572c56-0b4a-4c23-87d4-68001b7fe11a@s12g2000prs.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | sunpeng | 2010-10-15 00:18:16 | Re: how to write an optimized sql with two same subsql? |
Previous Message | Rob Sargent | 2010-10-15 00:06:11 | Re: how to write an optimized sql with two same subsql? |