title: Return multiple fields as a record in PostgreSQL with PL/pgSQL
body: <p>I am writing a SP, using PL/pgSQL.<br>
  I want to return a record, comprised of fields from several different tables. Could look something like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION get_object_fields(name text)
    RETURNS RECORD AS $$
  BEGIN
    -- fetch fields f1, f2 and f3 from table t1
    -- fetch fields f4, f5 from table t2
    -- fetch fields f6, f7 and f8 from table t3
    -- return fields f1 ... f8 as a record
  END
  $$ language plpgsql; 
  </code></pre>
  
  <p>How may I return the fields from different tables as fields in a single record?</p>
  
  <p><strong>[Edit]</strong></p>
  
  <p>I have realized that the example I gave above was slightly too simplistic. Some of the fields I need to be retrieving, will be saved as separate rows in the database table being queried, but I want to return them in the 'flattened' record structure.</p>
  
  <p>The code below should help illustrate further:</p>
  
  <pre><code>CREATE TABLE user (id int, school_id int, name varchar(32));
  
  CREATE TYPE my_type AS (
    user1_id   int,
    user1_name varchar(32),
    user2_id   int,
    user2_name varchar(32)
  );
  
  CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)
    RETURNS my_type AS $$
  DECLARE
    result my_type;
    temp_result user;
  BEGIN
    -- for purpose of this question assume 2 rows returned
    SELECT id, name INTO temp_result FROM user where school_id = schoolid LIMIT 2;
    -- Will the (pseudo)code below work?:
    result.user1_id := temp_result[0].id ;
    result.user1_name := temp_result[0].name ;
    result.user2_id := temp_result[1].id ;
    result.user2_name := temp_result[1].name ;
    return result ;
  END
  $$ language plpgsql
  </code></pre>