Обсуждение: trigger fails

Hello,

I want to trigger this function on insert to an existing table:

CREATE OR REPLACE FUNCTION get_final_energy (id text)
  RETURNS TABLE (
    jid text,
    "timestamp" text,
    machine text,
    scf integer,
    energy double precision
  )
  AS $function$
BEGIN
  RETURN query WITH a AS (
    SELECT
      --      public.results.jid AS ajid,
      regexp_split_to_table(public.results.totenergy_out, '\n') AS teo
    FROM
      public.results
    WHERE
      public.results.jid = id
),
b AS (
  SELECT
    public.results.jid,
    public.results. "timestamp" AS timestamp,
    public.results.machine AS machine,
    cast(
      CASE WHEN split_part(a.teo, ' ', 2) = '' THEN
        '0'
      ELSE
        split_part(a.teo, ' ', 2)
      END AS integer) AS scf,
    cast(
      CASE WHEN split_part(a.teo, ' ', 3) = '' THEN
        '0.0'
      ELSE
        split_part(a.teo, ' ', 3)
      END AS double precision) AS energy
  FROM
    public.results,
    a
  WHERE
    public.results.jid = id
  GROUP BY
    public.results.jid,
    public.results. "timestamp",
    public.results.machine,
    a.teo
),
c AS (
  SELECT DISTINCT ON (b.jid)
    b.jid AS jid,
    b. "timestamp" AS "timestamp",
    b.machine AS machine,
    b.scf AS scf,
    b.energy AS energy
  FROM
    b
  ORDER BY
    jid,
    scf DESC
)
SELECT
  *
FROM
  c RETURN;
END;
$function$
LANGUAGE plpgsql;

There are associated trigger functions:

CREATE OR REPLACE FUNCTION trigger_final_energy_table_create ()
  RETURNS TRIGGER
  AS $$
BEGIN
  CREATE TABLE IF NOT EXISTS final_energy (
    jid text,
    "timestamp" text,
    machine text,
    scf integer,
    energy double precision
  );
  RETURN new;
END;
$$
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION trigger_final_energy_table_insert ()
  RETURNS TRIGGER
  AS $$
BEGIN
  INSERT INTO final_energy
  SELECT
    *
  FROM
    get_final_energy (NEW.jid);
  RETURN new;
END;
$$
LANGUAGE 'plpgsql';

With these triggers:

CREATE TRIGGER atrigger_final_energy_table_create
  AFTER INSERT ON results
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_final_energy_table_create ();

CREATE TRIGGER btrigger_final_energy_table_insert
  AFTER INSERT ON results
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_final_energy_table_insert ();

All this code seems to run when I insert data into the 'results' table,
the 'final_energy' table gets created with the specifiled columns but no
data are inserted.

I have tried various modifications of get_final_energy without success.

If someone could point me to an abvious flaw or suggest how to debug
this it woould be most helpful.

Thanks for reding this long message,
Roger

I have tried various modifications of get_final_energy without success.

Hello,

David G. Johnston writes:

> On Sat, Aug 28, 2021 at 7:04 AM Roger Mason <rmason@mun.ca> wrote:
>
>>
>>     get_final_energy (NEW.jid);
>>
>>
> If you want to use this function within a trigger it should be redefined to
> accept "NEW" (i.e., the table record itself), not the jid column (IOW,
> don't perform a select on the table during the middle of the trigger
> execution).

I have not been able to get this to work: the trigger runs and the
trigger function is called but no new data are inserted into the target
table.  Having spent several days on this without result it is time to
give up.

Thanks again for your help.

Roger

On Sun, Aug 29, 2021 at 09:58:09AM -0230, Roger Mason wrote:
> I have not been able to get this to work: the trigger runs and the
> trigger function is called but no new data are inserted into the target
> table.  Having spent several days on this without result it is time to
> give up.

Can you provide whole, testable sql script? With create table, and test
insert, that should generate something in the target table, that is
missing?

Best regards,

depesz

On Mon, Aug 30, 2021 at 09:34:46AM -0230, Roger Mason wrote:
> > Can you provide whole, testable sql script? With create table, and test
> > insert, that should generate something in the target table, that is
> > missing?

it's better to reply to mailing list, unless it's a secret, as others
can comment and possibly provide answer.

In any case, after running your queries, I got:

ERROR:  relation "results" does not exist

This was on test script line 78, which was:

CREATE TRIGGER trigger_f_energy_insert AFTER INSERT ON results FOR EACH ROW
EXECUTE PROCEDURE trigger_f_energy_insert ();

So, can't test it, sorry.

Best regards,

depesz

Hello again,

hubert depesz lubaczewski writes:

> it's better to reply to mailing list, unless it's a secret, as others
> can comment and possibly provide answer.
>
> In any case, after running your queries, I got:
>
> ERROR:  relation "results" does not exist
>
> This was on test script line 78, which was:
>
> CREATE TRIGGER trigger_f_energy_insert AFTER INSERT ON results FOR EACH ROW
> EXECUTE PROCEDURE trigger_f_energy_insert ();
>
> So, can't test it, sorry.

This should be sufficient:

create table results (timestamp text,jid text,machine text,info_out
text);

Again please don't waste too much time on this.

Best wishes,
Roger

On Mon, Aug 30, 2021 at 11:03:33AM -0230, Roger Mason wrote:
> Hello again,
> 
> hubert depesz lubaczewski writes:
> 
> > it's better to reply to mailing list, unless it's a secret, as others
> > can comment and possibly provide answer.
> >
> > In any case, after running your queries, I got:
> >
> > ERROR:  relation "results" does not exist
> >
> > This was on test script line 78, which was:
> >
> > CREATE TRIGGER trigger_f_energy_insert AFTER INSERT ON results FOR EACH ROW
> > EXECUTE PROCEDURE trigger_f_energy_insert ();
> >
> > So, can't test it, sorry.
> 
> This should be sufficient:
> 
> create table results (timestamp text,jid text,machine text,info_out
> text);

Now your script fails with:
ERROR:  column results.totenergy_out does not exist

If you want help, please, pretty please with cherry on top - make
*usable* test script. 
Write it, test by psql -f ..., and then send if it:
1. works
2. shows the problem

Best regards,

depesz

Hello,

hubert depesz lubaczewski writes:

> Now your script fails with:
> ERROR:  column results.totenergy_out does not exist
>
> If you want help, please, pretty please with cherry on top - make
> *usable* test script. 
> Write it, test by psql -f ..., and then send if it:
> 1. works
> 2. shows the problem

I apologise for sending an unworkable script and wasting your time.

Thank you for your help.  I now have a version that works correctly,
although with limited testing so far.

Best wishes,
Roger