Re: conditional IF statements in postgresql

[Pujol Mathieu <mathieu.pujol@xxxxxxxxxxxxx>]

Le 07/07/2014 12:48, Albe Laurenz a écrit :
> 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
Snippet Hi,
You can do that in a single statement
std::string lStatement;
lStatement += "DO $$\n"; lStatement += "BEGIN\n";
lStatement += "IF COL_LENGTH('protein_sequence','comment') IS NULL THEN\n";
lStatement += "ALTER TABLE protein_sequence ADD comment VARCHAR(500) \n";
lStatement += "ELSE\n";
lStatement += "UPDATE TABLE protein_sequence ADD comment VARCHAR(500)\n";
lStatement += "END IF;\n";
lStatement += "END;\n";
lStatement += "$$;\n";
res = PQexec(conn, lStatement .c_str());
Regards,
Mathieu