Subject: trigger fails

From: Roger Mason <[email-masked]>
Date: Sat, 28 Aug 2021 10:50:35 -0230

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

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 28 Aug 2021 07:38:53 -0700

> I have tried various modifications of get_final_energy without success.

If get_final_energy() isn’t working as you expect (and its definition is
highly suspect so I wouldn’t be surprised) then all of this talk about
triggers is just adding unnecessary distraction.  Don’t add triggers to the
mix until you know the components are all functioning correctly.

I’d also suggest not doing create table if not exists in a trigger.
Whatever code you use to install the triggers should create the table
immediately and then the trigger just assumes it is present.

As for debugging the big query, you just need to learn by doing.  Start
writing it again from scratch and when you get to a point where it provide
unusual results stop and figure out why.

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 28 Aug 2021 08:50:40 -0700

>     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).

David J.

---

From: Roger Mason <[email-masked]>
Date: Sun, 29 Aug 2021 09:58:09 -0230

Hello,

> don't perform a select on the table during the middle of the trigger

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

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 30 Aug 2021 10:17:18 +0200

> table.  Having spent several days on this without result it is time to

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

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 30 Aug 2021 14:09:48 +0200

> > insert, that should generate something in the target table, that is

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

---

From: Roger Mason <[email-masked]>
Date: Mon, 30 Aug 2021 11:03:33 -0230

Hello again,

> 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

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 30 Aug 2021 15:36:17 +0200

> create table results (timestamp text,jid text,machine text,info_out

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

---

From: Roger Mason <[email-masked]>
Date: Wed, 01 Sep 2021 15:26:07 -0230

Hello,

> Write it, test by psql -f ..., and then send if it:

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