[postgis-users] execute 'alter table' won't work

[Peter Bange]

Hi Mark,

Thank you for your quick reply. I already had the 'raise notice' you  
suggested in the code.
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.

Regards, Peter



The complete code is:

CREATE OR REPLACE FUNCTION afn_check_atb_comp1
   RETURNS text AS

$BODY$
--	INFO
--	This routine
--	1) checks if all pollutants described in atb_pollutants are  
present as columns
--	in atb_compositions. If not, they will be added. Columns are never  
dropped, so changing
--	a name in atb_pollutants will only result in adding a column.
--	2) updates all the 'others' fields when units are percentages

-- 	HISTORY
--	created bng april 2007
--	CALLED BY

DECLARE
	rec record;
	strSQL text;
	strFields text;
	strSep text;
	intStrange int;

BEGIN

--	Init
	strFields = '';
	strSep = '';
	for rec in select shortname from atb_pollutants loop
	--	loop over pollutants
		BEGIN
			if rec.shortname <> 'others' then
				strFields = strFields || strSep || rec.shortname;
				strSep = ' + ';
			end if;
			strSQL = 'alter table atb_compositions add column ' ||  
rec.shortname || ' real';
			raise notice '%', strSQL;
			execute strSQL;
			raise notice '... added';
		EXCEPTION
			when duplicate_column then
			--	take next
			raise notice '... already existing';
		END;
	end loop;

	strSQL = 'update atb_compositions set others = 100 - (' || strFields  
|| ') where units = ' || quote_literal('percentage');
	-- strSQL = 'update atb_compositions set others = 100 - (' ||  
strFields || ') ';
	-- raise notice '%', strSQL;
	execute strSQL;

--	Check the percentages found
	strSQL = 'select count(*) from atb_compositions where units = ' ||  
quote_literal('percentage') || ' and (others < 0 or others is null)';
	execute strSQL into intStrange;
	if intStrange = 1 then
		raise exception 'One percentage is not ok, check atb_compositions.';
		return '(Done with remarks)';
	elseif intStrange > 1 then
		raise exception '% percentages are not ok, check  
atb_compositions.', trim(to_char(intStrange,'9999'));
		return '(Done with remarks)';
	else
		return '(Done)';
	end if;
END
$BODY$
   LANGUAGE 'plpgsql';


>
> Hi Peter,
>
> Have you tried outputting the statement actually being executed, e.g.:
>
> ...
> ...
> strSQL = 'alter table atb_compositions add column ' ||  
> rec.shortname ||
> ' real';
> raise notice 'sql: %', strSQL;
> execute strSQL;
> ...
> ...
>
> Also you haven't given the complete code for the stored procedure -  
> for
> example, you may have specified incorrect variable types in the  
> DECLARE
> section or incorrect parameter definitions in the function declaration
> itself.
>
>
> Kind regards,
>
> Mark.
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

-- 
ARGOSS: your partner for atmospheric, marine & coastal information
P O Box 61
8325ZH Vollenhove The Netherlands
tel +31-527-242299 fax +31-527-242016
Web http://www.argoss.nl

Confidentiality Notice & Disclaimer
The contents of this e-mail and any attachments are intended only for  
the
use of the e-mail addressee(s) shown. If you are not that person, or  
one of
those persons, you are not allowed to take any action based upon it  
or to
copy it, forward, distribute or disclose the contents of it and you  
should
please delete it from your system.

ARGOSS Holding BV and its subsidiaries do not accept any liability  
for any
errors or omissions in the context of this e-mail or its attachments  
which
arise as a result of Internet transmission, nor accept liability for
statements which are those of the author and not clearly made on  
behalf of
ARGOSS.




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070418/72e6b36f/attachment.html>