public inbox for [email protected]  

help / color / mirror / Atom feed

From: Roger Mason <[email protected]>
To: pgsql-novice <[email protected]>
Subject: trigger fails
Date: Sat, 28 Aug 2021 10:50:35 -0230
Message-ID: <[email protected]> (raw)

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

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: trigger fails
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox