I really need help on this one, it's for my university project and I'm stuck.
The big idea is to create a trigger on a table that if the value requirement is not met it prints a message to the user and doesn't allow the insert statement to proceed.
I already have a doSQL(conn, "query") function that sends the query to the database and prints any results (if any). Any SELECTs, UPDATEs or INSERTs work just fine with this function, however, I have a problem with multiple lines queries.
For example this:
doSQL(conn, "CREATE OR REPLACE FUNCTION najnizsza_krajowa() RETURNS trigger AS $$ BEGIN IF NEW.pensja<1500 THEN SELECT 'Pensja poniżej najniższej krajowej'; END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql' ");
returns an error saying ERROR: syntax error at or near "END" indicating at END IF; (not the later END). After this I have a create trigger statement
doSQL(conn, "CREATE TRIGGER pensja_check BEFORE INSERT ON pracownik FOR EACH ROW EXECUTE PROCEDURE najnizsza_krajowa();");
which I guess should work, but as I keep messing something with my function it only says that the function is missing. What do I do wrong with the function?
EDIT: The doSQL function, as requested:
void doSQL(PGconn *polaczenie, char *komenda){
PGresult *rez;
printf("%s\n", komenda);
rez = PQexec(polaczenie, komenda);
printf("Status: %s\n", PQresStatus(PQresultStatus(rez)));
printf("Komunikaty: %s\n", PQresultErrorMessage(rez));
switch(PQresultStatus(rez)) {
case PGRES_TUPLES_OK:
printTuples(rez);
break;
case PGRES_FATAL_ERROR:
printf("Coś nie tak!");
break;
}
PQclear(rez);
}
Sorry for some polish names, hope this won't matter much. Thanks in advance for any help, this is really important for me.
The unescaped single quotes you detected are not the problem. You cannot SELECT in a plpgsql function without a target. You would use PERFORM for that, but that's still not what you want. RAISE an EXCEPTION:
To do this:
prints a message to the user and doesn't allow the insert statement to proceed.
Use instead:
CREATE OR REPLACE FUNCTION najnizsza_krajowa()
RETURNS trigger AS
$$
BEGIN
IF NEW.pensja < 1500 THEN
RAISE EXCEPTION 'Pensja poniżej najniższej krajowej';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql
Or you could RAISE a WARNING and RETURN NULL; to avoid the rollback.
And do not quote the language name: 'plpgsql'