How to pass the value of NEW using dollar quoting?

Question

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.

You don't actually need `crosstab()` for this. And using an insert **and** an update is overkill. This can be done with a single `insert` statement (btw: `serial` is a reserved word and should not be used as a column name) — , Jul 12 '16 at 06:01
@a_horse_with_no_name Thanks mate. Yes, its an overkill to have an insert and update. I followed yours and ErwinBrandstetter 's advise. — tacticz03, Jul 13 '16 at 12:33

score 2 · Accepted Answer · edited May 23 '17 at 12:08

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:

Insert text with single quotes in PostgreSQL

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

Are PostgreSQL column names case-sensitive?

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.

Hi @ErwinBrandstetter. I thought crosstab will just work on mine but yours are awesome. Thanks for your answer, your simplified code works. Thank you very much. You're the best. — tacticz03, Jul 13 '16 at 12:24
@tacticz03: `crosstab()` is very fast, but the overhead just does not pay for a single result row from max. 3 input rows. — Erwin Brandstetter, Jul 14 '16 at 01:51
Hi @erwinbrandstetter, I'm encountering some problem. I have noticed that insert on my two tables ( details and data table) was asynchronously executed. First insert was made on details table then insert row on data table. My problem is your provided answer cannot fetch the rows of the data table. Can you help me? — tacticz03, Jul 16 '16 at 09:55
@tacticz03: Obviously, you need to `INSERT` into `data` first. Or you need a completely different approach ... — Erwin Brandstetter, Jul 16 '16 at 13:15

How to pass the value of NEW using dollar quoting?

1 Answers1