IronPort-SDR: 
 +aeAKBExVVMdwys1B3BhVvyzI7t0wfpwseI9LwRier/x2NA8HR+6cp4X+nbeEdHsF//EMVXzxN
 rEn8TiwJapBvJIVIyEimoxaA8eNEF82HsDBLTnV2VYIgsoQE5Nm1VpAAnxXEevIfinPDn95aRM
 tJVD/7Llb9Gts8E7dKSL3pYvkJ0Nkz2zjr/Xb7/trzN0PcO/rOxU04lGYAXwW/ByHJl9YBiR8e
 ymbe7qJjamvCo6sDzNdI32kjhK8y7DdFL6dDD/tbuvvNGv+eFQ3t9mP3ZApfmyx0AFgG6Fdu3Q
 KaI=
IronPort-HdrOrdr: =?us-ascii?q?A9a23=3AjAueQqixudR10bt6SFgXgKZJtXBQXscji2?=
 =?us-ascii?q?hC6mlwRA09TyX4raCTdZsguiMc5Ax6ZJhCo7G90cu7L080nKQdibX5Vo3PYO?=
 =?us-ascii?q?CJggaVBb154ZCnyzPtHDCWzIVg6Zs=3D?=
User-agent: mu4e 1.5.6; emacs 27.2
From: Roger Mason <rmason@mun.ca>
To: pgsql-novice <pgsql-novice@postgresql.org>
Subject: trigger fails
Date: Sat, 28 Aug 2021 10:50:35 -0230
Message-ID: <y65sfyteme4.fsf@mun.ca>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/y65sfyteme4.fsf%40mun.ca>
Precedence: bulk

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