Hi > -----Original Message----- > From: TalGloz [mailto:glozmantal@xxxxxxxxx] > Sent: Mittwoch, 29. August 2018 11:12 > To: pgsql-general@xxxxxxxxxxxxxx > 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
