RE: Executing a Function with an INSERT INTO command fails

[Charles Clavadetscher]

Hi

> -----Original Message-----
> From: TalGloz [mailto:glozman...@gmail.com]
> Sent: Mittwoch, 29. August 2018 13:22
> To: pgsql-gene...@postgresql.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