Return a Record of Rows in PostgreSQL

Question

I'm attempting to write a SELECT query which returns a RECORD of ROWS.

I've solved half of the problem, as in I can get a RECORD to return a single ROW. But what about returning a set of ROWS?

Take the following query for example:

SELECT * FROM
  RECORD(
    (
      ROW(1::integer, 'Florida'::character varying)
    )
  ) AS tbl (id integer, state character varying)

I get what I anticipate:

+==============+
| id | state   |
+====+=========+
| 1  | Florida |
+----+---------+

However, when I attempt to add one additional ROW to the RECORD, this is where I encounter problems:

SELECT * FROM
  RECORD(
    (
      ROW(1::integer, 'Florida'::character varying),
      ROW(2::integer, 'Georgia'::character varying)
    )
  ) AS tbl (id integer, state character varying)

I would anticipate a result similar to:

+==============+
| id | state   |
+====+=========+
| 1  | Florida |
+----+---------+
| 2  | Georgia |
+----+---------+

However, I receive the following PostgreSQL errors instead:

********** Error **********
ERROR: function return row and query-specified return row do not match
SQL state: 42804
Detail: Returned type record at ordinal position 1, but query expects integer.

What is it I'm missing here? Something trivial, or am I approaching this all wrong? Any pointers or suggestions is greatly appreciated, and thank you!

On a related note: I understand that if this was to take place in a stored procedure I could return a SETOF, however for this use case I'm interested in returning a RECORD of ROWS directly through a query without the use of a stored procedure.

score 5 · Accepted Answer · 2013-06-01 11:29:28Z

5

I'm not sure how to do this using the row constructor, but using the values clause, seems a lot easier (and cleaner) to me:

select * 
from (
  values 
    (1, 'Florida'),
    (2, 'Georgia')
) AS tbl (id, state);

or using a CTE

with tbl (id, state) as (
  values 
     (1, 'Florida'),
     (2, 'Georgia')
) 
select * 
from tbl;

The use of the RECORD keyword turns both rows into a single record if I'm not mistaken. A record can never hold more than one row.

Edit (after Craig's comment):

To complete the picture, the following would return exactly the same result albeit without the possibility to specify column names:

values 
  (1, 'Florida'),
  (2, 'Georgia');

This can be used anywhere a SELECT can be used.

edited Jun 1, 2013 at 11:29

answered May 31, 2013 at 21:01

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Joshua Burns Over a year ago

I believe this does actually cover my use case! Maybe I have a misunderstanding on how RECORDs work, but I am still very curious how one might utilize ROWs. Thanks much for your time. :)

user330315 Over a year ago

@JoshuaBurns: actually I think you cannot use RECORD outside of PL/pgSQL but I'm not entirely sure. But RECORD always represents a single row (record). So I don't think this will work in any way.

Craig Ringer Over a year ago

The VALUES construct is the correct way to do this. You actually don't need the WITH or SELECT; VALUES is a valid stand-alone query.

user330315 Over a year ago

@CraigRinger: how would you give the columns of the values clause a name without using the select or the cte?

Craig Ringer Over a year ago

@a_horse_with_no_name You don't, and they're assigned default names column1, column2 ... column*n*. Depending on the calling context you might not care. I'm only pointing this out because people don't often realise that VALUES is a stand-alone statement; it's certainly more useful to wrap it but understanding why that works can be valuable.

Collectives™ on Stack Overflow

Return a Record of Rows in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related