How do I 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, Mar 24 '16 at 12:07
Ah, I think aliasing the function as t is the key to my problem! Thanks! — PhilHibbs, Mar 24 '16 at 12:16

score 5 · Answer 1 · answered Mar 24 '16 at 11:55

5

SELECT the_date, the_bool FROM util.retrec();

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

answered Mar 24 '16 at 11:55

Berry Langerak

18,561
4
45
58

Abelisto · Answer 2 · 2016-03-24T12:32:00.260

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

How do I access record elements in postgresql?

2 Answers2