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 13:22
> To: pgsql-general(at)postgresql(dot)org
> 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