CODEWITHSUNDEEP
postgresqlfunctionboolean
Cipango
Cipango

Reputation: 31

Postgresql function update

I have 2 tables :

  1. the first for the calendar with date, and open day (boolean)

  2. the second is a table of holidays.

I made a function for change the status of open-day on the calendar :

CREATE OR REPLACE FUNCTION add_hol ()
RETURNS boolean AS
UPDATE calendar SET open_day = 'false'
WHERE day IN (
SELECT jour FROM calendar AS c
INNER JOIN calendar_hol AS h
ON c.day=h.day_hol)
LANGUAGE sql

But have always the error message :

syntax error at or near "UPDATE".

How to correct this, please ? (i tried in a many ways, but with no results...)

Upvotes: 0

Views: 325

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324571

You need to quote the function body. You can use normal ' quoting and double all 's within the function body, or you can use the PostgreSQL extension $$ quoting.

Update: Also, as it returns no result, it should be declared RETURNS void.

CREATE OR REPLACE FUNCTION add_hol ()
RETURNS void AS
$$
UPDATE calendar SET open_day = 'false'
WHERE day IN (
SELECT jour FROM calendar AS c
INNER JOIN calendar_hol AS h
ON c.day=h.day_hol)
$$
LANGUAGE sql;

See the manual for details.

Update since you're looking for a trigger procedure but didn't say so.

This doesn't make any sense as a trigger. I think you are actually trying to modify a row as it is inserted or updated. To do that, you must set values in the NEW variable.

A wild guess, but something like this might be what you are looking for:

CREATE OR REPLACE FUNCTION add_hol ()
RETURNS trigger AS
$$
BEGIN
  IF tg_op = 'INSERT' OR tg_op = 'UPDATE'
  THEN

    NEW.open_day = NOT EXISTS (
      SELECT 1
      FROM calendar_hol AS h
      WHERE h.day_hol = NEW.day
    );

    RETURN NEW;

  END IF;
END;
$$
LANGUAGE plpgsql;

This is a wild guess based on missing information. It might be totally wrong. If you are still stuck after reading the manual as linked in the comments, please post a new question with a proper explanation of the problem you are trying to solve.

Upvotes: 1

Related Questions