RE: Executing a Function with an INSERT INTO command fails

["Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx>]

Hi

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