RE: Executing a Function with an INSERT INTO command fails

Hi

> -----Original Message-----
> From: TalGloz [mailto:glozmantal(at)gmail(dot)com]
> Sent: Mittwoch, 29. August 2018 11:12
> To: pgsql-general(at)postgresql(dot)org
> Subject: Executing a Function with an INSERT INTO command fails
>
> 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?

No. It is possible to use a normal function.
Do you get any error?

Does the select deliver any result at all?

If yes, is there maybe already a trigger on table public.runtime_benchmark?

If not, is there maybe a policy on either public.nyc2015_09_enc or public.runtime_benchmark?

If not, could you provide more information on the table?

Regards
Charles

> Best regards,
> Tal
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html