Re: conditional IF statements in postgresql

Madhurima Das wrote:
> I am writing a C program to access a PostgreSQL database, where
> I add a column if it doesn't exists in the table
> or, update the column, if the column already exits.
> Please suggest how to work with the conditional statements.

> N.B. I wrote the following:
> 
> res = PQexec(conn, "IF COL_LENGTH('protein_sequence','comment') IS NULL");
> PQclear(res);
> if(res)
>  {
>      res = PQexec(conn, "ALTER TABLE protein_sequence ADD comment VARCHAR(500)");
>      PQclear(res);
>  }
>  else
>  {
>      res = PQexec(conn, "UPDATE TABLE protein_sequence ADD comment VARCHAR(500)");
>       PQclear(res);
>  }
> 
> Is the code logically correct??

No, that doesn't make any sense.
The statement sent with PQexec must be a legal SQL statement.

You could do it like this:

/* try the update */
res = PQexec(conn, "UPDATE protein_sequence SET comment = ... WHERE ...");
if (!res) {
    /* out of memory, error out */
}
r = PQresultStatus(res);
PQclear(res);
if (r == PGRES_COMMAND_OK) {
    return;  /* UPDATE ok */
} else if (r != PGRES_NONFATAL_ERROR) {
    /* unexpected result, error out */
}

/* add the column */
res = PQexec(conn, "ALTER TABLE protein_sequence ADD comment VARCHAR(500)");
if (!res) {
    /* out of memory, error out */
}
r = PQresultStatus(res);
PQclear(res);
if (r == PGRES_COMMAND_OK) {
    return;  /* ALTER TABLE ok */
} else {
    /* unexpected result, error out */
}

This code is untested.

Yours,
Laurenz Albe