Hi
> -----Original Message-----
> From: TalGloz [mailto:[email protected]]
> Sent: Mittwoch, 29. August 2018 13:22
> To: [email protected]
> Subject: RE: Executing a Function with an INSERT INTO command fails
>
> Charles Clavadetscher wrote
> > Do you get any error?
>
> The function executes perfectly and does what it's supposed to except of the
> INSERT INTO part. I don't get any
> errors.
>
>
> > Does the select deliver any result at all?
>
> Yes, booth SELECT deliver everything they supposed to.
>
>
> > If yes, is there maybe already a trigger on table
> > public.runtime_benchmark?
>
> I didn’t crate any triggers for the table manually and I don't think they are
> crated automatically.
>
>
> > If not, is there maybe a policy on either public.nyc2015_09_enc or
> > public.runtime_benchmark?
>
> There are no security policy on any of them, at least I didn't set any while
> creating the tables with PgAdmin4.
> I use the tables on a local server for testing purposes only.
>
>
> > If not, could you provide more information on the table?
>
> What kind of information should I provide?
The point is to try to reproduce the problem. So the table definition as
delivered by \d would be a good starting point. Or the CREATE TABLE generated
by pgAdmin.
While I was having a closer look at the function I noticed that you call
another function to populate the sealArray.
In order to try to reproduce the error I did:
Create public.nyc2015_09_enc on assumptions and populate it with some data.
CREATE TABLE public.nyc2015_09_enc
(
id INTEGER,
"Pickup_longitude" TEXT,
"Dropoff_longitude" TEXT
);
INSERT INTO public.nyc2015_09_enc VALUES (1,'47.0','8.0');
INSERT INTO public.nyc2015_09_enc VALUES (2,'49.0','8.5');
SELECT * FROM public.nyc2015_09_enc;
id | Pickup_longitude | Dropoff_longitude
----+------------------+-------------------
1 | 47.0 | 8.0
2 | 49.0 | 8.5
(2 rows)
Create public.runtime_benchmark based on assuptions.
CREATE TABLE public.runtime_benchmark
(
test_number INTEGER,
column_names TEXT,
execution_time TEXT,
operation_type TEXT,
seal_or_sql TEXT
);
Create your function. The sealArray creation is modified, because I don't know
how the function public.seal_diff_benchmark is defined.
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));
sealArray := ARRAY[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;
Is there any reason for the loop 1..2?
And test it.
SELECT * FROM public.runtime_benchmark ;
test_number | column_names | execution_time | operation_type | seal_or_sql
-------------+--------------+----------------+----------------+-------------
(0 rows)
SELECT * FROM seal_diff_benchmark_pgsql('0.12');
seal_diff_benchmark_pgsql
---------------------------
47.0
49.0
(2 rows)
SELECT * FROM public.runtime_benchmark ;
test_number | column_names | execution_time |
operation_type | seal_or_sql
-------------+-------------------------------------+----------------+----------------+-------------
1 | Pickup_longitude, Dropoff_longitude | 8.0 | sub
| seal
1 | Pickup_longitude, Dropoff_longitude | 8.5 | sub
| seal
(2 rows)
Unfortunately I am not able to reproduce the problem, but maybe with the table
and functions definitions, as well as the 2 data rows that are selected in the
function, is that easier to analyze.
Regards
Charles
> Best regards,
> Tal
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
