User function that returns a set of rows. - Mailing list pgsql-sql
| From | David Harel |
|---|---|
| Subject | User function that returns a set of rows. |
| Date | |
| Msg-id | 4BFAC67F.8050504@gmail.com Whole thread Raw |
| Responses |
Re: User function that returns a set of rows.
(Joshua Tolley <eggyknap@gmail.com>)
|
| List | pgsql-sql |
<font size="+1"><font face="Arial">Hi,<br /><br /> I'm struggling to write what seed at the time a simple function<br />like:<br /> "CREATE FUNCTION foo() RETURNS IDONO as<br /> $$<br /> select * from tbl;<br /> return (whatever that willgive the *);<br /> $$<br /><br /> up till now I got this far:<br /></font></font><p class="MsoNormal" dir="LTR" style="text-align:left; direction: ltr; unicode-bidi: embed;"><font face="Courier New" size="2"><span style="font-size: 10pt;font-family: "Courier New";">CREATE OR REPLACE FUNCTION select_business_types ()</span></font><p class="MsoNormal" dir="LTR"style="text-align: left; direction: ltr; unicode-bidi: embed;"><font face="Courier New" size="2"><span style="font-size:10pt; font-family: "Courier New";">RETURNS SETOF RECORD </span></font><p class="MsoNormal" dir="LTR" style="text-align:left; direction: ltr; unicode-bidi: embed;"><font face="Courier New" size="2"><span style="font-size: 10pt;font-family: "Courier New";">AS</span></font><p class="MsoNormal" dir="LTR" style="text-align: left; direction: ltr;unicode-bidi: embed;"><font face="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";">$body$</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><fontface="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";">DECLARE</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><fontface="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";"> rec RECORD;</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><font face="CourierNew" size="2"><span style="font-size: 10pt; font-family: "Courier New";">-- fields business_type_id business_type_name</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><fontface="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";">BEGIN</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><fontface="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";"> FOR </span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><font face="CourierNew" size="2"><span style="font-size: 10pt; font-family: "Courier New";"> rec IN SELECT * FROM sv_bo_business_types</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><fontface="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";"> LOOP</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><font face="CourierNew" size="2"><span style="font-size: 10pt; font-family: "Courier New";"> RETURN NEXT rec;</span></font><pclass="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><font face="CourierNew" size="2"><span style="font-size: 10pt; font-family: "Courier New";"> END LOOP;</span></font><p class="MsoNormal"dir="LTR" style="text-align: left; direction: ltr; unicode-bidi: embed;"><font face="Courier New" size="2"><spanstyle="font-size: 10pt; font-family: "Courier New";"> RETURN;</span></font><p class="MsoNormal" dir="LTR"style="text-align: left; direction: ltr; unicode-bidi: embed;"><font face="Courier New" size="2"><span style="font-size:10pt; font-family: "Courier New";">END;</span></font><p class="MsoNormal" dir="LTR" style="text-align: left;direction: ltr; unicode-bidi: embed;"><font face="Courier New" size="2"><span style="font-size: 10pt; font-family: "CourierNew";">$body$</span></font><p class="MsoNormal" dir="LTR" style="text-align: left; direction: ltr; unicode-bidi:embed;"><font face="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";"></span></font><fontface="Courier New" size="2"><span style="font-size: 10pt; font-family: "Courier New";">LANGUAGE'plpgsql';</span></font><br /><font size="+1"><font face="Arial">When I tried it from the shell I got a nastyerror message about that I am not in an environment to receive a set ??? (can't see it now. Office restrictions).<br/><br /> Any idea?<br /></font></font> <pre class="moz-signature" cols="72">-- Regards. David Harel, ================================== Home office +972 77 7657645 Cellular: +972 54 4534502 Snail Mail: Amuka D.N Merom Hagalil 13802 Israel Email: <a class="moz-txt-link-abbreviated" href="mailto:hareldvd@ergolight-sw.com">hareldvd@ergolight-sw.com</a> </pre>