PostgreSQL: Accessing fields of record types

Question

I have a function that returns a record

CREATE OR REPLACE FUNCTION atPeriod(
    IN segment segmentST,
    IN period period,
    OUT n integer,
    OUT s segmentST,
    OUT p pointST)
  RETURNS record AS
'$libdir/Hermes', 'atPeriodSegmentSTV1'
  LANGUAGE c STABLE STRICT
  COST 1;

I call that function in another function

CREATE OR REPLACE FUNCTION distance(seg1 segmentST, seg2 segmentST) RETURNS realTS AS
$BODY$
DECLARE 
    projseg1 record;
    projseg2 record;
BEGIN
    SELECT atPeriod(seg1,p) INTO projseg1; 
    SELECT atPeriod(seg2,p) INTO projseg2; 
    IF (projseg1).n = 2 AND (projseg2).n = 2 THEN
        ...
    END IF;
END;
$BODY$ LANGUAGE plpgsql;

When I execute the second function I receive the error

********** Error **********

ERROR: record "projseg1" has no field "n"
SQL state: 42703

If instead I call the first function in a SQL statement

WITH Rec AS (
select 2 as seg_id, atPeriod(SegmentST(
pointst('2012-01-01 08:00:00', 1, 2),
pointst('2012-01-01 08:02:00', 2, 2)),
period('2012-01-01 08:00:00', '2012-01-01 08:01:00')
) as seg
)
Select (seg).n from rec

I receive the value 2 and no error.

Any hint how to solve this issue ?

Translate this n'a pas pu identifier la colonne « n » dans le type de données de l'enregistrement — Vivek S.
– Vivek S., Commented Apr 22, 2015 at 10:07
@vivek It's actually rather handy to have errors in the original language (though of course English summaries are nice). The original message can be grepped for in the source code after doing a quick lookup in the translation files to get the English message template it came from. A translation usually cannot be. — Craig Ringer
– Craig Ringer, Commented Apr 22, 2015 at 11:16
@CraigRinger I don't understand which Language is that if I knew then I can use translator, In this case only OP or the one who can read that language only understand what it means :) — Vivek S.
– Vivek S., Commented Apr 22, 2015 at 11:42
@vivek I mean you can use the PostgreSQL sources, without needing to know anything except the text. e.g. git grep -B 4 -A 4 'pas pu identifier la colonne' shows that it's the message-template could not identify column \"%s\" in record data type from parser/parse_expr.c:407 — Craig Ringer
– Craig Ringer, Commented Apr 22, 2015 at 11:47

pozs · Accepted Answer · 2015-04-22 10:24:53Z

1

This happens, because your projseg1 & projseg2 variables are records, so they'll automatically configure themselves to the row type of the query result columns (which is exactly row(row(n, s, p)) but not, what you want: row(n, s, p)).

To select only the row (which is not encapsulated into another row), you should use:

SELECT * INTO projseg1 FROM atPeriod(seg1, p);
-- or:
projseg2 := atPeriod(seg2, p);

answered Apr 22, 2015 at 10:24

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Accessing fields of record types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related