Executing a Function with an INSERT INTO command fails - Mailing list pgsql-general

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