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.

Ridge

Here is few examples: select (retrec()).p1; select t.*, t.p1, t.p2 from retrec() t; select * from retrec(); – Endogenous

Ah, I think aliasing the function as t is the key to my problem! Thanks! – Ridge

Answer 1

SELECT the_date, the_bool FROM util.retrec();

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

Dimitry

Answer 2

*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.

Isoclinal

Answer 3

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

Endogenous