Re: conditional IF statements in postgresql

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