function error

[bijunator]

CREATE OR REPLACE FUNCTION which_code(v_in text)
RETURNS integer AS
$BODY$
declare
v_temp integer := 0;
v_return integer := 99;
begin
begin
v_temp := v_in::integer;
exception
when others then
v_temp := 99;
raise notice 'The first two characters of CTRCODE is not numeric : %', v_in;
end;
if v_temp between 1 and 9 then v_return := 1;
elsif v_temp between 10 and 14 then v_return := 2;
elsif v_temp between 15 and 17 then v_return := 3;
elsif v_temp between 18 and 39 then v_return := 4;
elsif v_temp between 40 and 49 then v_return := 5;
elsif v_temp between 50 and 51 then v_return := 6;
elsif v_temp between 52 and 59 then v_return := 7;
elsif v_temp between 60 and 67 then v_return := 8;
elsif v_temp between 68 and 89 then v_return := 9;
elsif v_temp between 90 and 97 then v_return := 10;
else v_return := 99; end if;
return v_return;
end;
$BODY$
LANGUAGE plpgsql;
----------------------------------------------------------------------------------
ERROR: cannot have more than 100 subtransactions in a transaction (seg25 avvppbli038:50001 pid=4841)
DETAIL: PL/pgSQL function "bxg30_which_industry" line 5 during statement block entry

********** Error **********

ERROR: cannot have more than 100 subtransactions in a transaction (seg25 avvppbli038:50001 pid=4841)
SQL state: 54M30
Detail: PL/pgSQL function "which_code" line 5 during statement block entry

Why!!!

[carlo.ascani@2ndquadrant.it]

The problem here is not in the function code itself.

Greenplum will not allow you to have more than 100 savepoint in a transaction.
That's due to its shared nothing architecture.

My advice here is to check who and how invoke this function.
It is very important to "close" all savepoint as long as they are "done".

[bijunator]

But there is no DML happening here. I am just calling this function from a select statement.
i.e., create table my_table as select col1,col2,col3,...,which_code(col25) as col25,...,col99 from some_table where date = '01-jan-2012' distributed by (col1);

[carlo.ascani@2ndquadrant.it]

Hi,

the BEGIN ... END block generates a SAVEPOINT, that is a subtransaction.

I have reproduced the error. The main problem here is that seems END; does not clean SAVEPOINTs.

Would you please try these two things to confirm that?

1. Comment the BEGIN...END block in function, this way:

--BEGIN
    v_temp := v_in::integer;
--EXCEPTION when others THEN
--      v_temp := 99;
--      RAISE NOTICE 'The first two characters of CTRCODE is not numeric : %', v_in;
--END;

2. Limit rows returned by the SELECT

create table my_table as select col1,col2,col3,...,which_code(col25) as col25,...,col99 from some_table where date = '01-jan-2012' LIMIT 50 distributed by (col1);

Cheers,

[bijunator]

Oh..I got your point. The more rows the select returns, the begin end thus forms that many subtransactions. Well, there is no use of this function if that begin-end is commented out as I wanted to bypass the erroneous CTRCODE else my whole table creation fails. Rather I got another way to go ahead with it --
which_code((case when substr(col25,1,2) ~ '[0-9][0-9]' then substr(col25,1,2)::int else 99 end)::int) so when ever there is an erroneous CTRCODE, it would give back the default value, so the table creation would not fail. Also removed the explicit typecasting from the function.

Thanks...

[carlo.ascani@2ndquadrant.it]

Nice solution. Cheers