PostgreSQL and Libpq - Create trigger that prints a line when requirement is not met

Question

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.

Is there any reason you can't use psql or PgAdmin to submit your create statement? — Kuberchaun, Apr 01 '14 at 15:45
Yes, it is required for my project. I know, kind of stupid, but I can't do anything about it. — Asunez, Apr 01 '14 at 15:46
When I run your create function in PgAdmin with only removing the starting double quote and ending double quote it creates without error. You need to provide the source to doSQL. — Kuberchaun, Apr 01 '14 at 15:49
@Bob Edited the question, now it contains the doSQL function. — Asunez, Apr 01 '14 at 15:56
Okay, after looking at the first line for a long time I noticed where I made a mistake. `SELECT 'Pensja poniżej najniższej krajowej'` should be `SELECT \'Pensja poniżej najniższej krajowej\'`. This is what messed everything up. Ps: As I cannot answer my questions now I will post the answer here and do it the right way after 8 hours (low reputation limit). — Asunez, Apr 01 '14 at 16:05

score 0 · Accepted Answer · edited May 23 '17 at 11:57

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'. It's an identifier, not a string.

PostgreSQL and Libpq - Create trigger that prints a line when requirement is not met

1 Answers1