Home > mailing lists

Thread: conditional IF statements in postgresql

conditional IF statements in postgresql

From

Madhurima Das

Date:

07 July 2014, 03:32:16

Hi,

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.

Thanks!

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??

Re: conditional IF statements in postgresql

From

David G Johnston

Date:

07 July 2014, 03:47:24

madhu_d wrote
> Hi,
>
> 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.
>
> Thanks!
>
> 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??

Not by any logic that I find recognizable.  It is also absolutely not
syntactically correct.

I have no clue why you think the updating of the column is conditional.  I
can understand needing to add a missing column before you can effect an
update but that can and should be independent of the need to update the
column.

Neither "IF" nor "UPDATE TABLE ... ADD" are valid commands that you can
issue directly via PQExec.

The only valid commands are listed here:

http://www.postgresql.org/docs/9.3/interactive/sql-commands.html

Any other commands, of which conditionals are a subset, must be executed
within the context of a DO command or user-defined function.  In particular
you should see if pl/pgsql can be made to accomplish that which you need.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/conditional-IF-statements-in-postgresql-tp5810687p5810691.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: conditional IF statements in postgresql

From

Albe Laurenz

Date:

07 July 2014, 10:48:37

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

Re: conditional IF statements in postgresql

From

Pujol Mathieu

Date:

07 July 2014, 11:44:50

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

Re: conditional IF statements in postgresql

From

Pujol Mathieu

Date:

07 July 2014, 15:46:09

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

Re: conditional IF statements in postgresql

From

Andy Colson

Date:

07 July 2014, 16:36:26

On 7/6/2014 10:47 PM, David G Johnston wrote:
> madhu_d wrote
>> Hi,
>>
>> 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.
>>
>> Thanks!
>>
>> 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??
>
> Not by any logic that I find recognizable.  It is also absolutely not
> syntactically correct.
>
> I have no clue why you think the updating of the column is conditional.  I
> can understand needing to add a missing column before you can effect an
> update but that can and should be independent of the need to update the
> column.
>
> Neither "IF" nor "UPDATE TABLE ... ADD" are valid commands that you can
> issue directly via PQExec.
>
> The only valid commands are listed here:
>
> http://www.postgresql.org/docs/9.3/interactive/sql-commands.html
>
> Any other commands, of which conditionals are a subset, must be executed
> within the context of a DO command or user-defined function.  In particular
> you should see if pl/pgsql can be made to accomplish that which you need.
>
> David J.
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/conditional-IF-statements-in-postgresql-tp5810687p5810691.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


 > Neither "IF" nor "UPDATE TABLE ... ADD" are valid commands that you can
 > issue directly via PQExec.


I'm guessing that's Transact-SQL, which is microsoft only.

You'll want to query out from information_schema:

select *
from information_schema.columns
where table_name = 'protein_sequence'
and column_name = 'comment';

Then fire off appropriate alter statements based on that.

-Andy

Re: conditional IF statements in postgresql

From

Pujol Mathieu

Date:

08 July 2014, 07:01:56

Le 07/07/2014 18:28, Madhurima Das a écrit :

Hi Pujol,

Thanks a ton for your help!!

I was missing the semicolon and it works fine now..

Thanks,
Madhurima
On Mon, Jul 7, 2014 at 11:09 AM, Madhurima Das <madhurima.das@gmail.com> wrote:
I just checked that anything after the line
ALTER TABLE protein_sequence ADD comment VARCHAR(500) \n\
does not work and gives the same syntax error as above.
On Mon, Jul 7, 2014 at 10:56 AM, Madhurima Das <madhurima.das@gmail.com> wrote:
Thanks once again.. However, I get a error after running the program as:

Adding col to table (ALTER) Failed: ERROR: syntax error at or near "ELSE"
LINE 5: ELSE
^

Can you provide some suggestions.
On Mon, Jul 7, 2014 at 10:46 AM, Pujol Mathieu <mathieu.pujol@realfusio.com> wrote:
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

Hi,
I'm glad that helps you. Just feew tips for using the mailing list. You should reply to the mailing list pgsql-general@postgresql.org( and not only the person who answers you), so that other users could see that your problem is solve. You should also write your answer at the bottom of the mail to facilitate reading.
Regards,
Mathieu