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