Syntax error at CREATE TRIGGER query

Question

I have this code, to add trigger for a View in my postgresql database

CREATE OR REPLACE FUNCTION changeCityProc() RETURNS TRIGGER AS $changeCity$
    BEGIN
    UPDATE vvs.tb_company SET city = "LAL" WHERE cardpresso.cardinfo.tb_company_city = vvs.tb_company.city;
    RETURN null;
    END;
$changeCity$ LANGUAGE plpgsql;




CREATE TRIGGER mytrigger
    INSTEAD OF UPDATE ON
      cardpresso.cardinfo FOR EACH ROW EXECUTE PROCEDURE changeCityProc();

pgAdmin says "syntax error at or near "INSTEAD""

The correct syntax is INSTEAD OF, but I would expect the error to be at UPDATE instead of INSTEAD. — Gordon Linoff
– Gordon Linoff, Commented May 29, 2014 at 14:20
Maybe the docs could help a little: postgresql.org/docs/9.1/static/sql-createtrigger.html — iCantSeeSharp
– iCantSeeSharp, Commented May 29, 2014 at 14:21
Please, update the code to match exactly the code with the error. — iCantSeeSharp
– iCantSeeSharp, Commented May 29, 2014 at 14:26
Do you have column "LAL" in your tables? If not, then it's better to use single quotes for string literal. — vyegorov
– vyegorov, Commented May 29, 2014 at 14:36

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-05-30 11:12:01Z

1

Let's simplify this. First, the two schemas.

CREATE SCHEMA cardpresso;
CREATE SCHEMA vvs;

Next, the simplest possible table.

CREATE TABLE vvs.tb_company
(
  city character varying(25)
);
INSERT INTO vvs.tb_company VALUES ('foo');

And a fairly useless view.

CREATE VIEW cardpresso.cardinfo AS
  SELECT 'test' as tb_company_city UNION ALL
  SELECT 'Orem' UNION ALL
  SELECT 'Wibble'
;

Simplify the function. Warning: This will destructively update the table. Be careful if you copy stuff from this function into something headed for production.

CREATE OR REPLACE FUNCTION changecityproc()
  RETURNS trigger AS
$BODY$
    BEGIN
    UPDATE vvs.tb_company SET city = 'bar';
    RETURN null;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Finally, the trigger.

CREATE TRIGGER mytrigger
  INSTEAD OF UPDATE
  ON cardpresso.cardinfo
  FOR EACH ROW
  EXECUTE PROCEDURE changecityproc();

Now, if we update the view, we'd expect all the rows in vvs.tb_company to change to 'bar'.

select * from vvs.tb_company;

city
--
foo

update cardpresso.cardinfo
set tb_company_city = 'wibble';

select * from vvs.tb_company;

city
--
bar

So it looks like the problem is in the UPDATE statement of your function.

UPDATE vvs.tb_company SET city = "LAL" 
WHERE cardpresso.cardinfo.tb_company_city = vvs.tb_company.city;

I'm not certain what you intended that statement to do, but it's not valid in PostgreSQL, regardless of whether "LAL" is supposed to be a column name or a string literal. As a string literal, 'LAL', it will raise an error.

ERROR:  missing FROM-clause entry for table "cardinfo"
LINE 2:     WHERE cardpresso.cardinfo.tb_company_city = vvs.tb_compa...

answered May 30, 2014 at 11:12

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Alexander Capone Over a year ago

Thank you very much, i have small question: i have to add Where OLD.cty='someconstantvalue' to change only those who has someconstantvalue So i have to chnage FOR EACH ROW as well ? please tell to what if yes:)

Mike Sherrill 'Cat Recall' Over a year ago

No, you don't need to change FOR EACH ROW to FOR EACH STATEMENT as long as you're still using an "instead of" trigger. Test your SQL update statement in a SQL window (psql, pgAdmin, etc) before you try to use it in a trigger function. When you create a trigger function, PostgreSQL doesn't try to determine whether the SQL statement is valid; it trusts you to do that.

Alexander Capone Over a year ago

UPDATE vvs.tb_company SET city = 'surprice' where OLD.city='foo'; in table i got 5 foo cities and 5 bar cities after trigger works i got 10 suprise cities, when i wanted to have 5 surprises and 5 foo...how do i do it?

Mike Sherrill 'Cat Recall' Over a year ago

If you wanted to keep your 5 "foo" cities, you shouldn't have used where OLD.city='foo'. But your update statement shouldn't have set all values to 'surprise' in any case. It sounds like you wanted to do something more like UPDATE vvs.tb_company SET city = 'surprise' where OLD.city='bar';.

Alexander Capone Over a year ago

yes, sorry, i meant that. in result of this function, i get 10 surprise after Updating view either way...

|

Collectives™ on Stack Overflow

Syntax error at CREATE TRIGGER query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related