<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><DIV><BLOCKQUOTE type="cite"></BLOCKQUOTE><DIV><BR class="khtml-block-placeholder"></DIV>Hi Mark,</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Thank you for your quick reply. I already had the 'raise notice' you suggested in the code.</DIV><DIV>But what I found out now, is that when I drop all the code from 'end loop' the code works fine ! So as a work around I have split the routine in two parts ...., and that works. But this is rather mysterious to me, but maybe clear for you. </DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Regards, Peter</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>The complete code is:</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>CREATE OR REPLACE FUNCTION afn_check_atb_comp1</DIV><DIV> RETURNS text AS</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>$BODY$</DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>INFO</DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>This routine </DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>1) checks if all pollutants described in atb_pollutants are present as columns</DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>in atb_compositions. If not, they will be added. Columns are never dropped, so changing</DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>a name in atb_pollutants will only result in adding a column.</DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>2) updates all the 'others' fields when units are percentages</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>-- <SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>HISTORY</DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>created bng april 2007</DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>CALLED BY</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>DECLARE</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>rec record;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strSQL text;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strFields text;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strSep text;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>intStrange int;</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>BEGIN</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>Init</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strFields = '';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strSep = '';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>for rec in select shortname from atb_pollutants loop</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>loop over pollutants</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>BEGIN</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>if rec.shortname <> 'others' then</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strFields = strFields || strSep || rec.shortname;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strSep = ' + ';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>end if;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strSQL = 'alter table atb_compositions add column ' || rec.shortname || ' real';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>raise notice '%', strSQL;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>execute strSQL;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>raise notice '... added';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>EXCEPTION</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>when duplicate_column then</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>take next</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>raise notice '... already existing';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>END;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>end loop;</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strSQL = 'update atb_compositions set others = 100 - (' || strFields || ') where units = ' || quote_literal('percentage');</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>-- strSQL = 'update atb_compositions set others = 100 - (' || strFields || ') ';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>-- raise notice '%', strSQL;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>execute strSQL;</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>--<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>Check the percentages found</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>strSQL = 'select count(*) from atb_compositions where units = ' || quote_literal('percentage') || ' and (others < 0 or others is null)';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>execute strSQL into intStrange;</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>if intStrange = 1 then</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>raise exception 'One percentage is not ok, check atb_compositions.';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>return '(Done with remarks)';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>elseif intStrange > 1 then</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>raise exception '% percentages are not ok, check atb_compositions.', trim(to_char(intStrange,'9999'));</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>return '(Done with remarks)';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>else</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>return '(Done)';</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>end if;</DIV><DIV>END</DIV><DIV>$BODY$</DIV><DIV> LANGUAGE 'plpgsql';</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV><BR><BLOCKQUOTE type="cite"><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Hi Peter,</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Have you tried outputting the statement actually being executed, e.g.:</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">...</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">...</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">strSQL = 'alter table atb_compositions add column ' || rec.shortname ||</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">' real';</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">raise notice 'sql: %', strSQL;</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">execute strSQL;</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">...</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">...</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Also you haven't given the complete code for the stored procedure - for</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">example, you may have specified incorrect variable types in the DECLARE</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">section or incorrect parameter definitions in the function declaration</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">itself.</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Kind regards,</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Mark.</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">postgis-users mailing list</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV> </BLOCKQUOTE></DIV><BR><DIV> <SPAN class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">-- </DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">ARGOSS: your partner for atmospheric, marine & coastal information</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">P O Box 61</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">8325ZH Vollenhove The Netherlands</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">tel +31-527-242299 fax +31-527-242016</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Web <A href="http://www.argoss.nl">http://www.argoss.nl</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Confidentiality Notice & Disclaimer</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">The contents of this e-mail and any attachments are intended only for the</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">use of the e-mail addressee(s) shown. If you are not that person, or one of</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">those persons, you are not allowed to take any action based upon it or to</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">copy it, forward, distribute or disclose the contents of it and you should</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">please delete it from your system.</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">ARGOSS Holding BV and its subsidiaries do not accept any liability for any</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">errors or omissions in the context of this e-mail or its attachments which</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">arise as a result of Internet transmission, nor accept liability for</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">statements which are those of the author and not clearly made on behalf of</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">ARGOSS.</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV><BR class="khtml-block-placeholder"></DIV><BR class="Apple-interchange-newline"></SPAN> </DIV><BR></BODY></HTML>