Insert a Count(*) value into a variable in Postgresql

Question

I am migrating from Mysql to Postgresql and trying to create the following Trigger:

    CREATE OR REPLACE FUNCTION verificaReservaQuarto()
RETURNS trigger
AS $$
    begin
        declare ocupado int;
declare consultaReserva int;
select count(*) into @consultaReserva from reserva_quarto R where R.dataSaida is null and R.dataEntrada is not null and new.idConsulta = R.idConsulta;
select count(*) into @ocupado from reserva_quarto R where R.dataSaida is null and R.dataEntrada is not null and new.idQuarto = R.idQuarto;
set new.dataSaida = null;
if (new.dataEntrada < now() or (@ocupado > 0) or (@consultaReserva > 0)) then
????????????????????????????
else 
return NEW;
end if;
 end;
$$ LANGUAGE plpgsql;

However, the following error is occurring:

ERROR:  syntax error at or near "*"
LINE 7: select count(*) into @consultaReserva from reserva_quarto R ...
               ^
CONTEXT:  invalid type name "count(*) into @consultaReserva from reserva_quarto R where R.dataSaida is null and R.dataEntrada is "
SQL state: 42601
Character: 149

Where I put "????????????????" I would not let the insertion take place, because in my business rule the room would be occupied.

Follow the tables used on Rigger:

CREATE TABLE reserva_quarto (
dataEntrada timestamp not null,
dataSaida timestamp,
idConsulta INTEGER not null,
idQuarto INTEGER not null,
FOREIGN KEY(idQuarto) REFERENCES quartos (idQuarto),
FOREIGN KEY(idConsulta) REFERENCES consulta (idConsulta)
);

CREATE TABLE consulta (
idConsulta SERIAL PRIMARY KEY,
prescricao VARCHAR(1000) not null,
dataConsulta timestamp not null ,
exame int not null,
idFuncGeral INTEGER not null,
idPaciente INTEGER not null,
idMedico INTEGER not null,
FOREIGN KEY(idMedico) REFERENCES medicos (idMedico)
);

CREATE TABLE quartos (
idQuarto SERIAL PRIMARY KEY,
frigobar INTEGER not null,
TV INTEGER not null,
idAla INTEGER not null
);

Answer 1

Invert the variable assignment with the into, thus:

select into consultaReserva count(*) from reserva_quarto

Also, your variable does not have the @ in the name (this is used in sql-server), so you don’t need this

Answer 2

Considering you’re a Rigger BEFORE, I think your code should look something like this:

CREATE OR REPLACE FUNCTION verificaReservaQuarto()
RETURNS SETOF TRIGGER AS 
$$
DECLARE
   ocupado BOOLEAN;
   consultaReserva BOOLEAN;
BEGIN

   PERFORM true
   FROM reserva_quarto r 
   WHERE r.dataSaida IS NULL 
   AND r.dataEntrada IS NOT NULL 
   AND NEW.idConsulta = r.idConsulta;

   consultaReserva = FOUND;

   PERFORM true
   FROM reserva_quarto r 
   WHERE r.dataSaida IS NULL 
   AND r.dataEntrada IS NOT NULL 
   AND NEW.idQuarto = r.idQuarto;

   ocupado = FOUND;

   NEW.dataSaida = null;

   IF NEW.dataEntrada < CURRENT_TIMESTAMP OR ocupado OR consultaReserva THEN
      RAISE EXCEPTION 'Não é possível fazer a reserva pois o quarto está ocupado.';
   ELSE
      RETURN NEW;
   END IF;

END;
$$ 
LANGUAGE plpgsql;

Note that I am using the variable FOUND and using the command PERFORM instead of SELECT. I did this to optimize the Query, since there is no need to return the result of COUNT. Thus returns true whether it has found any record and false if he didn’t find.

Here you can find more information about when and how to use the command PERFORM: Executing a Command with No Result