Executing a Function with an INSERT INTO command fails

[TalGloz <glozmantal@xxxxxxxxx>]

Hello,

I have this function that executes a C extention function in it and returns
a SETOF TEXT

    CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams
CHARACTER VARYING) RETURNS SETOF TEXT AS $outputVar$
    DECLARE
    	tempVar1 CHARACTER VARYING;
    	tempVar2 CHARACTER VARYING;
        outputVar text;
    	sealArray TEXT[];
    	outputArray TEXT[];
    BEGIN
    	FOR i IN 1..2 LOOP
    		SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, tempVar2
FROM public.nyc2015_09_enc WHERE id=i;
    		sealArray := (SELECT public.seal_diff_benchmark(tempVar1, tempVar2,
sealparams));
    		outputArray[i] := sealArray[1];

         	INSERT INTO public.runtime_benchmark (test_number, column_names,
execution_time, operation_type, seal_or_sql) VALUES (1, 'Pickup_longitude,
Dropoff_longitude', sealArray[2], 'sub', 'seal');

    	END LOOP;

    	FOREACH outputVar IN ARRAY outputArray LOOP
    		RETURN NEXT outputVar;
    	END LOOP;
    END;
    $outputVar$ LANGUAGE plpgsql;

Inside the first FOR LOOP... I've an INSERT INTO... command but nothing gets
inserted into the public.runtime_benchmark table. Executing the INSERT INTO
command separately works without any problems so why doesn't it work inside
my PostgreSQL function?

The user executing the function above with the INSERT command is alsow the
owner of the public.runtime_benchmark table. Do I have to create a trigger
function for the public.runtime_benchmark table to be able to insert into it
using some other function? Or is there a simpler way to modify the function
above to achieve my goal?

Best regards,
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html