<div xmlns="http://www.w3.org/1999/xhtml">Selamlar,</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">İstediğiniz çıktıyı verecek olan SQL sorgusu aşağıdaki şekilde;</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><blockquote xmlns="http://www.w3.org/1999/xhtml"><div>select ed.id, ed.master_id, ed.evrak_no, array_agg(syer.yer_adi) from tbl_detay ed left join tbl_std_yer syer on syer.id=any(ed.yer_id_array) group by ed.id, ed.master_id, ed.evrak_no;</div></blockquote><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">Bu çıktıyı daha önce iletmiş olduğunuz fonksiyon örneği içerisine yerleştirir isek;</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><blockquote xmlns="http://www.w3.org/1999/xhtml"><div>create or replace function sp_list_abc(in sp_mastter_id integer, out sp_id bigint, out sp_evrakno text, out sp_yer_adi_array text[])</div><div>returns setof record language sql as</div><div>$body$</div><div>select ed.id, ed.evrak_no, array_agg(syer.yer_adi) from tbl_detay ed left join tbl_std_yer syer on syer.id=any(ed.yer_id_array) where ed.master_id=$1 group by ed.id, ed.master_id, ed.evrak_no;</div><div>$body$;</div></blockquote><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">Kullanım örneği de aşağıdaki gibi;</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><blockquote xmlns="http://www.w3.org/1999/xhtml"><div>postgres=# select * from sp_list_abc(1);<br /> sp_id | sp_evrakno | sp_yer_adi_array<br />-------+------------+------------------<br /> 3 | yyy5 | {Denizli,İzmir}<br /> 2 | abcd78 | {İzmir}<br />(2 rows)<br /><br />postgres=# select * from sp_list_abc(12);<br /> sp_id | sp_evrakno | sp_yer_adi_array <br />-------+------------+-------------------------<br /> 1 | 12ilerd | {İzmir,İstanbul,Ankara}<br />(1 row)<br /> </div></blockquote><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">İyi çalışmalar.</div><div xmlns="http://www.w3.org/1999/xhtml">Samed YILDIRIM</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">27.11.2018, 21:03, "Java Notlarım" <javanotlarim(at)gmail(dot)com>:</div><blockquote xmlns="http://www.w3.org/1999/xhtml" type="cite"><div><p style="margin-bottom:0.5cm;line-height:100%;background:transparent;"><span style="font-variant-east-asian:normal;font-variant-numeric:normal;"><font color="#222222"><font face="Arial, Helvetica, sans-serif"><font style="font-size:12pt;">Merhaba, öncelikle ilginiz ve yardımınız için çok teşekkür ederim. </font></font></font></span><br /><span style="font-variant-east-asian:normal;font-variant-numeric:normal;"><font color="#222222"><font face="Arial, Helvetica, sans-serif"><font style="font-size:12pt;">Can Bey; ARRAY_AGG kullandığımda "array_agg(text, unknown) fonksiyonu mevcut değildir" uyarısı aldım.</font></font></font></span><br /><br /><span style="font-variant-east-asian:normal;font-variant-numeric:normal;"><font color="#222222"><font face="Arial, Helvetica, sans-serif"><font style="font-size:12pt;">Samed bey; Postgresql 10 versiyonunu kullanıyorum. Tablolarım ve örnek verileri excel tablosundan kopyalayıp yapıştırdım, umarım mail içeriğinde sorun olmaz.</font></font></font></span></p><p style="margin-bottom:0.5cm;line-height:100%;background:transparent;"> </p><table style="font-family:"Liberation Sans";font-size:x-small;" cellspacing="0" border="0"><tbody><tr><td colspan="4" style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" valign="middle" height="17">tbl_detay</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">İd – bigint</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">master_id – integer</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">evrak_no – text</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">yer_id_array - smallint []</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">1</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">12</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">12ilerd</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">{3,2,4}</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">2</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">1</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">abcd78</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">{2,5}</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">3</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">1</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">yyy5</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">{1.2}</td></tr></tbody></table> <div> </div><div><table style="font-family:"Liberation Sans";font-size:x-small;" cellspacing="0" border="0"><tbody><tr><td colspan="2" style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" valign="middle" height="17">tbl_std_yer</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">İd – integer</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">yer_adi - text</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">1</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">Denizli</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">2</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">İzmir</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">3</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">İstanbul</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">4</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">Ankara</td></tr></tbody></table></div><div><table style="font-family:"Liberation Sans";font-size:x-small;" cellspacing="0" border="0"><tbody><tr><td colspan="4" style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" valign="middle" height="17">Function sonucu istenilen</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">İd – bigint</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">master_id – integer</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">evrak_no – text</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">yer_adlari</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">1</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">12</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">12ilerd</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">{İstanbul,İzmir,Denizli}</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">2</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">1</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">abcd78</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">{İzmir,Bursa}</td></tr><tr><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center" height="17">3</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">1</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">yyy5</td><td style="border-width:1px;border-style:solid;border-color:rgb(0,0,0);font-family:"Liberation Sans";" align="center">{Denizli.İzmir}</td></tr></tbody></table></div></div> <div><div>Samed YILDIRIM <<a rel="noopener noreferrer" href="mailto:samed(at)reddoc(dot)net">samed(at)reddoc(dot)net</a>>, 27 Kas 2018 Sal, 19:11 tarihinde şunu yazdı:</div><blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div>Merhabalar,</div><div> </div><div>Tabloların (tbl_detay ve tbl_std_yer) kolon yapılarını (create cümlecikleri de olur), birkaç satırlık örnek veri ve kullandığınız PostgreSQL versiyonunu da ekler misiniz.</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>27.11.2018, 19:56, "Java Notlarım" <<a target="_blank" rel="noopener noreferrer" href="mailto:javanotlarim(at)gmail(dot)com">javanotlarim(at)gmail(dot)com</a>>:</div><blockquote type="cite"><div>Merhaba. <div>Tablomda smallint array tipinde bir alanım mevcut. Bu alandaki verileri çekerken, smallint değerleri yerine, bu smallint değerlerin string karşılıkları ile çekmek için, JOIN kullanarak bir sorgu yazdım. Bu sorgu normalde sorunsuz çalışırken, sorguyu function (Stored Procedure) olarak yazmaya çalıştığımda </div><div> </div><div><strong>"ERROR: HATA: record dönüşlü bildirilmiş işlevde return deyimin tipi uyumsuz AYRINTI: Son ifade 3 sütununda text[] yerine text döndürüyor."</strong></div><div>şeklinde bir hata alıyorum. Aldığım hata ile ilgili çözüm öneriniz olursa çok memnun olurum.</div><div>Functionum aşağıdaki gibi</div><div> </div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">CREATE OR REPLACE FUNCTION sp_list_abc(</blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">IN sp_master_id integer,</blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">OUT sp_id bigint,</blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">OUT sp_evrakno text,<span style="white-space:pre-wrap;"> </span></blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">OUT sp_yer_adi_array text[])</blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"> RETURNS SETOF record </blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"> LANGUAGE 'sql'</blockquote></blockquote><div> </div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"> COST 100</blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"> VOLATILE </blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"> </blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">AS $BODY$</blockquote> <blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">select <a target="_blank" rel="noopener noreferrer" href="http://ed.id/">ed.id</a>, ed.evrak_no,STRING_AGG(syer.yer_adi, ', ') </blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">from tbl_detay ed</blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">LEFT JOIN tbl_std_yer syer on <a target="_blank" rel="noopener noreferrer" href="http://syer.id/">syer.id</a>=ANY(ed.yer_id_array)</blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">where ed.master_id=$1 </blockquote><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">GROUP BY <a target="_blank" rel="noopener noreferrer" href="http://ed.id/">ed.id</a>, syer.yer_adi</blockquote> <blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">$BODY$;</blockquote></blockquote></div></blockquote></blockquote></div></blockquote>