Execute SQL Task didn't work correctly with functions

Hi,

i've found a new problem.
On Greenplum i've made a function. I will start this function with SSIS "Execute SQL Task".
The Task finished successfully in one second, normaly the function works for over one minute!

<!-- s:!: --><img src="{SMILIES_PATH}/icon_exclaim.gif" alt="" title="Exclamation" /><!-- s:!: --> A lookup in the database shows, that the function didn't run in reality <!-- s:!: --><img src="{SMILIES_PATH}/icon_exclaim.gif" alt="" title="Exclamation" /><!-- s:!: -->

Here is the Function:

CREATE OR REPLACE FUNCTION misc.ysp_full_tabledistribution()
RETURNS text AS
$BODY$
--XXX
DECLARE
integer_var int = 0;
tablename_var text;
variable_name text;
SQL text;

BEGIN
truncate table misc.tabledistribution;

WHILE integer_var < (select max(id) from misc.tables) LOOP

integer_var = integer_var + 1;

tablename_var = (select tablename from misc.tables where id = integer_var);

variable_name = '''' || tablename_var || '''';

SQL = 'insert into misc.tabledistribution select ''' || tablename_var ||''' , gp_segment_id, count(*) anzahlimsegment from ' || tablename_var || ' group by gp_segment_id';

EXECUTE SQL;

END LOOP;
return SQL;
--return tablename_var;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION misc.ysp_full_tabledistribution()
OWNER TO misc_role;

I do not know what has to be done, that the funciton will executed in a normal way.

I tried to reproduce the issue using a similar procedure and a SSIS task, and it worked as expected. Could you try the following:
1. make sure that the latest build of the PGNP OLEDB Provider is used;
2. simplify the stored procedure, and see if the simple version works, e.g.3. inspect Greenplum logs (and/or PGNP Profiler's trace), and see if call to the stored procedure succeeded.

Thank for your reply.
I didn't worked. That's strange.


--i ve created the table.
drop table if exists misc.tbl;
create table misc.tbl(fld varchar(30));

select *
from misc.tbl
--result: completely clean.




--i add your code in this function.
CREATE OR REPLACE FUNCTION misc.fn_tbl_my_test()
RETURNS text AS
$BODY$
DECLARE
SQL text;

--Code from PGOleDB
BEGIN
SQL = 'insert into misc.tbl(fld) values(''my test'')';
EXECUTE SQL;
return SQL;
END;

$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION misc.ysp_full_tabledistribution()
OWNER TO misc_role;


--call in SSIS (no result)
select misc.fn_tbl_my_test()

select *
from misc.tbl
--completely clean


--call in PGAdminIII (insert one line like expected).
select misc.fn_tbl_my_test()

select *
from misc.tbl
--there is one line like expected.

Do you know where i can find the greenplum logs?

If i start an analyze database function in execute SQL Task, it works!
"truncate table" works too.

I guess this issue could be related to lack of Greenplum permissions. Reason why the stored proc works in PGAdmin is that a different user is logged in the PGAdmin. Could you double-check if permissions granted to the right users?

Please refer to Greenplum Admin Guide for location of the logs. I think they can be found by the command: $ find /gpmaster -name *.log

I tried with the GPAdmin Account. It didn't work.
I will search for the GP logs today. Maybe there is the answer.

Now it works!

The function has to be called without "select" and ";".
Resultset = "None"

The return of the function must be set to "1", like the example:

CREATE OR REPLACE FUNCTION misc.ysp_analyse_tabledistribution()
RETURNS integer AS
$BODY$
DECLARE
BEGIN

truncate table misc.analyse_tabledistribution;

--ta ta ta

return 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION misc.ysp_analyse_tabledistribution()
OWNER TO misc_role;


Thx, Daniel