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 06:55:08 |
Message-ID: | c78e3df4-1eab-4271-9cb8-e3afe5ebdf7b@a7g2000prb.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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-(dot)(dot)(dot)(at)burntmail(dot)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(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;
>
> --
> Guy Rouillier
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Rasheed | 2010-10-15 07:08:05 | Re: input out of error with haversine formula |
Previous Message | Greg Stark | 2010-10-15 06:36:30 | Re: [GENERAL] pg_filedump binary for CentOS |