RE: Executing a Function with an INSERT INTO command fails

Hi

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