UDT returned by function - ERROR: column "sire" not found in data type

Hi All,

Im using postgresql 9.3

here is ‘type’ sql

SET client_encoding TO ‘UTF8’;
\set ON_ERROR_STOP ON
CREATE TYPE hy_parents AS OBJECT (
“DAM” varchar(30), “SIRE” varchar(30), “G_DAM” varchar(30), “PROG_ORIGIN” varchar(30)
);

here is function sql

SET client_encoding TO ‘UTF8’;
\set ON_ERROR_STOP ON
CREATE OR REPLACE FUNCTION get_parents (inmk text) RETURNS HY_PARENTS AS $body$
DECLARE
OBJPARS hy_parents;
BEGIN
/Dam, Sire, Grand Dam/
Select into objpars Main_Key as DAM, Male_Key as SIRE, Par_Key as G_DAM, coalesce(Prog_Origin, PROG_LINE) as PROG_ORIGIN
from Mate
Where selected=0 AND (prog_gen, prog_line, Prog_Code, Prog_Location) In (SELECT Gen, Line, Sire, Dam
From Gen A
WHERE A.MAIN_KEY=INMK);
Return(ObjPars);
End;
$body$
LANGUAGE PLPGSQL
STABLE;

It works. when I run command I got the results.
db=# select Get_Parents(‘02016XX9705’);
get_parents

(02015YY12707,02015YY5787,02014ZZ3303,Y:Z)


but when I want to select only one field from UDT I got error

db=# select (Get_Parents(‘02016XX9705’)).SIRE;
ERROR: column “sire” not found in data type hy_parents


Can You help?
Thank you,

caveman

Hallo caveman,

ich habe noch nicht mit eigenen Typen gearbeitet. Aber ich vermute es liegt an der Definition der Felder mit “”.
Wenn du

select (Get_Parents('02016XX9705'))."SIRE";

versuchst, würde ich wetten das es geht.

Gruß frastr

try

select (Get_Parents('02016XX9705'))."SIRE";

the problem is you have defined it as “SIRE”, but called SIRE, postgresql is case insensitive. It would be better for your you not to use " for column-names and so on.

this is a german speaking forum, please let us switch to german, okay?

Frastr, akretschmer es funktioniert!
Vielen Dank.
Leider bin ich nicht deutschsprachig, aber Regeln sind Regeln.
Ich werde das nächste Mal versuchen Google Übersetzer

Sei glücklich und gesund,
Höhlenmensch


frastr, akretschmer it works !
Thank you very much.
Unfortunately I’m not german speaking person but rules are rules.
I will try next time google translator

be happy and healthy,
caveman

you are welcome, no problem at all.