Search Postgresql Archives

Re: conditional IF statements in postgresql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Title: Snippet

Le 07/07/2014 13:44, Pujol Mathieu a écrit :


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



Hi,
My answer is a C++ sample.
This looks like you don't have include string header, or use this code if if you are writing pure C program.
const char * lStatement = "\
DO $$\n\
BEGIN\n\
IF COL_LENGTH('protein_sequence','comment') IS NULL THEN\n\
ALTER TABLE protein_sequence ADD comment VARCHAR(500) \n\
ELSE\n\
UPDATE TABLE protein_sequence ADD comment VARCHAR(500)\n\
END IF;\n\
END;\n\
$$;\n"; 

Regards,
Mathieu

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux