How to access record elements in PostgreSQL?

Question

PostgreSQL v8.2 (Greenplum)

CREATE OR REPLACE FUNCTION util.retrec(OUT p1 date, OUT p2 boolean)
RETURNS RECORD
AS
$BODY$
DECLARE
BEGIN
 p1 := current_date;
 p2 := true;
 RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

SELECT util.retrec();

This returns (2016-03-24,t) - how do I pull those two values out individually?

I can do it interactively with SELECT p1,p2 FROM util.retrec(); but how do I assign the two values into two variables in a procedure? I tried this:

SELECT util.retrec() INTO r1, r2;

No luck, this tries to assign the record into r1.

Here is few examples: select (retrec()).p1; select t.*, t.p1, t.p2 from retrec() t; select * from retrec(); — Abelisto
– Abelisto, Commented Mar 24, 2016 at 12:07
Ah, I think aliasing the function as t is the key to my problem! Thanks! — PhilHibbs
– PhilHibbs, Commented Mar 24, 2016 at 12:16

Berry Langerak · Accepted Answer · 2016-03-24 11:55:05Z

5

SELECT the_date, the_bool FROM util.retrec();

Just list the fields with their names, that ought to work.

answered Mar 24, 2016 at 11:55

Berry Langerak

18.9k5 gold badges49 silver badges62 bronze badges

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

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2024-01-24 16:05:15Z

*Memos:

You cannot easily access the element of a named type row(record) different from an anonymous type row(record).
My answer explains how to access the element of an anonymous type row(record).

For example, you create person table, then insert 2 rows into it as shown below:

CREATE TABLE person (
  id INTEGER,
  name VARCHAR(20),
  age INTEGER
);

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32);

Then, you create my_func() function which returns a named type row(record) with person_row as shown below:

CREATE FUNCTION my_func()
RETURNS RECORD
AS $$
DECLARE
  person_row RECORD; -- Here
BEGIN
  SELECT * INTO person_row FROM person WHERE id = 2;
  RETURN person_row;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func()
RETURNS RECORD
AS $$
DECLARE
  person_row person%ROWTYPE; -- Here
BEGIN
  SELECT * INTO person_row FROM person WHERE id = 2;
  RETURN person_row;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() gets a named type row(record) as shown below:

postgres=# SELECT my_func();
   my_func
--------------
 (2,David,32)
(1 row)

Now, you can access the element of the named type row(record) with to_json(), to_jsonb() and row_to_json() as shown below:

postgres=# SELECT to_json(my_func())->'id' AS id;
 id
----
 2
(1 row)

postgres=# SELECT to_jsonb(my_func())->'name' AS name;
  name
---------
 "David"
(1 row)

postgres=# SELECT row_to_json(my_func())->'age' AS age;
 age
-----
 32
(1 row)

*Memos:

You can omit AS clause.
The doc explains these functions.

In addition, you can use trim() to trim " from "David" as shown below:

postgres=# SELECT trim(BOTH '"' FROM (to_json(my_func())->'name')::VARCHAR(20)) AS name;
 name
-------
 David
(1 row)

postgres=# SELECT trim(BOTH '"' FROM (to_jsonb(my_func())->'name')::VARCHAR(20)) AS name;
 name
-------
 David
(1 row)

postgres=# SELECT trim(BOTH '"' FROM (row_to_json(my_func())->'name')::VARCHAR(20)) AS name;
 name
-------
 David
(1 row)

*Memos:

You can omit AS clause.
Be careful, omitting ::VARCHAR(20) gets the error.

Abelisto · Accepted Answer · 2016-03-24 12:32:00Z

0

Here is several ways how to do it:

do $$
declare
  r1 date;
  r2 boolean;
  r record;
begin
  r := retrec();
  raise info '%', r;
  raise info '%', r.p1;
  raise info '%', r.p2;

  select * into r from retrec();
  raise info '%', r;
  raise info '%', r.p1;
  raise info '%', r.p2;

  select * into r1, r2 from retrec();
  raise info '%, %', r1, r2;
end; $$ language plpgsql;

Note that it was tested on PostgreSQL 9.5

edited Mar 24, 2016 at 12:32

answered Mar 24, 2016 at 12:20

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to access record elements in PostgreSQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related