Re: conditional IF statements in postgresql

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.
Snippet

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