Databases
StackOverflow2013
Postsdbo
POReturn multiple fields as a record in PostgreSQL with PL/pgSQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturn multiple fields as a record in PostgreSQL with PL/pgSQL
    text
    copied!<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 ( 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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload