CODEWITHSUNDEEP
postgresqlplpgsqldatabase-trigger
dor
dor

Reputation: 25

ERROR: column "exists" does not exist in SQL insert command

I'm trying to learn about triggers a bit and I want to make a trigger that will check if im adding new row to my prediction table that made of (city,eventype,casualties)

that if my new row casualties number is higher than the city population i will get an error and the new row wont be inserted into my prediction table

here is my code so far

creating my Trigger

create trigger T1
before insert on prediction
for each row
execute procedure trigf1();

creating trigf1();

create or replace function trigf1() returns trigger as $$
declare bad_population record;
Begin
  select city.cname INTO bad_population
  from city
  where new.casualties>city.population and new.cname=city.cname;

  if exists bad_population then begin 
    raise notice 'bad population-more casualites than city population';
    return null;
  end;
  else
    return new;
  end if;
end;
$$language plpgsql;

im getting an error when i try to enter these values 

insert into prediction values ('Naples', 'Volcano', 3056)

and this is the error 

ERROR:  column "exists" does not exist
LINE 1: SELECT exists bad_population
               ^
QUERY:  SELECT exists bad_population
CONTEXT:  PL/pgSQL function trigf1() line 7 at IF
SQL state: 42703

my City Table

my City Table

my prediction table

my prediction table

would love your help understanding the error and solving my problem

Upvotes: 1

Views: 1507

Answers (1)

404
404

Reputation: 8572

Use FOUND: IF FOUND THEN.

If there were any results from your query, FOUND will be TRUE, else FALSE.

EXISTS takes a query which returns TRUE or FALSE depending on whether the query returns any results, e.g. IF EXISTS (SELECT 1 FROM table WHERE condition) THEN...

So the main difference is FOUND is a variable which is set after your query/statement returns, and is set to TRUE/FALSE depending on whether your SELECT statement returned any results, or your UPDATE/DELETE statement affected any rows.

EXISTS on the other hand is an operator that returns TRUE/FALSE based on a query you provide to it at that point in time.

Upvotes: 3

Related Questions