tacticz03 tacticz03 - 4 months ago 14
SQL Question

How to pass the value of NEW using dollar quoting?

I cannot access the value of the

NEW
row inside my
crosstab()
query string.

CREATE OR REPLACE FUNCTION insert_fx()
RETURNS TRIGGER AS
$BODY$
BEGIN
INSERT INTO outputtb (serial,date, judge)
VALUES (NEW.serial, NEW.date, NEW.tjudge) RETURNING serial INTO newserial;

UPDATE outputtb
SET (reading1,
reading2,
reading3) =
(SELECT ct."reading1",
ct."reading2",
ct."reading3"
FROM crosstab( $$
SELECT tb2. serial,tb2. readings,tb2. value
FROM DATA AS tb2
INNER JOIN outputtb AS tb1 USING (serial)
WHERE tb2.serial = $$||NEW.serno||$$
ORDER BY 1 ASC $$, $$
VALUES ('reading1'),('reading2'),('reading3')$$
) ct ("Serial" VARCHAR(50),"Reading1" FLOAT8, "Reading2" FLOAT8, "Reading3" FLOAT8))
WHERE sn = NEW.serno;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER insert_tg
BEFORE INSERT ON details
FOR EACH ROW EXECUTE PROCEDURE insert_fx();


It returns this error:


ERROR: syntax error at or near "CC1027HCA0GESKN00CC000FT0000"
LINE 6: tb2. serial = 043611007853619CC1027HCA0GESKN00CC000FT...



I think it does not accept characters, it only accepts integers. Maybe the quoting need some modification and I'm not that familiar with pgsql quoting.

I need help to finish my project. I'm stuck on this part.

Answer

The immediate cause of the error message is that you concatenated the string NEW.serno without quoting it. To safely fix use format() or quote_literal() or quote_nullable().

...
   UPDATE outputtb
   SET           (reading1,    reading2,    reading3)
     = (SELECT ct.reading1, ct.reading2, ct.reading3
        FROM   crosstab(
           'SELECT serial, t2.readings, t2.value
            FROM   data     t2
            JOIN   outputtb t1 USING (serial)
            WHERE  serial = ' || quote_nullable(NEW.serno) || '
            ORDER  BY 1'
          , $$VALUES ('reading1'),('reading2'),('reading3')$$
            ) ct (serial text, reading1 float8, reading2 float8, reading3 float8))
   WHERE  sn = NEW.serno; 
...

Basics:

In passing I also fixed your incorrect mixed-case identifiers:

But there are more problems:

  • newserial has not been declared and is also not used.
  • outputtb is pointless noise in the query passed to crosstab().
  • Like @a_horse commented, you shouldn't need an INSERT and an UPDATE, and crosstab() also seems like overkill.

This is a big mess.


Going out on a limb, my educated guess is you want this:

CREATE OR REPLACE FUNCTION insert_fx()
  RETURNS TRIGGER AS
$func$
BEGIN
   INSERT INTO outputtb (serial, date, judge, reading1, reading2, reading3)
   SELECT NEW.serial, NEW.date, NEW.tjudge, ct.*
   FROM  (SELECT 1) dummy
   LEFT   JOIN crosstab (
     'SELECT serial, readings, value
      FROM   data
      WHERE  serial = ' || quote_nullable(NEW.serno) || '
      ORDER  BY 1'
    , $$VALUES ('reading1'),('reading2'),('reading3')$$
      ) ct (serial text, reading1 float8, reading2 float8, reading3 float8) ON true;

   RETURN NEW; 
END
$func$  LANGUAGE plpgsql;

The LEFT JOIN to a dummy table prevents losing the INSERT when crosstab() comes up empty.

Which can be simplified to:

CREATE OR REPLACE FUNCTION insert_fx()
  RETURNS TRIGGER AS
$func$
BEGIN
   INSERT INTO outputtb (serial, date, judge, reading1, reading2, reading3)
   SELECT NEW.serial, NEW.date, NEW.tjudge
          min(value) FILTER (WHERE readings = 'reading1')
          min(value) FILTER (WHERE readings = 'reading2')
          min(value) FILTER (WHERE readings = 'reading3')
   FROM   data
   WHERE  serial = NEW.serno;

   RETURN NEW; 
END
$func$  LANGUAGE plpgsql;

Since we aggregate now, a result row is guaranteed, and we don't have to defend against losing it.

Aside: "serial" is not a reserved word. But it's the name of a common pseudo data-type, so I still wouldn't use it as column name to avoid confusing error situations.