It it possible to get this result in one query? - Mailing list pgsql-general

From Nick
Subject It it possible to get this result in one query?
Date
Msg-id 75dd976e-1825-4204-a634-49c5cb4fa06e@z30g2000prg.googlegroups.com
Whole thread Raw
List pgsql-general
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;

pgsql-general by date:

Previous
From: Nick
Date:
Subject: Re: It it possible to get this result in one query?
Next
From: Nick
Date:
Subject: Re: It it possible to get this result in one query?