Postgres Pro
Downloads
Home   >   mailing lists

Thread: IF NOT EXIST

IF NOT EXIST

From
Igor Korot
Date:
Hi, ALL,
I have a following statement:

IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS
ns ) CREATE FUNCTION();

Unfortunately trying to execute it thru the ODBC interface with:

ret = SQLExecDirect( m_hstmt, query, SQL_NTS );

gives syntax error near IF.

What is the proper way to do that?

Thank you.

Re: IF NOT EXIST

From
Pavel Stehule
Date:
Hi

út 18. 12. 2018 v 7:11 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
Hi, ALL,
I have a following statement:

IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS
ns ) CREATE FUNCTION();

Unfortunately trying to execute it thru the ODBC interface with:

ret = SQLExecDirect( m_hstmt, query, SQL_NTS );

gives syntax error near IF.

What is the proper way to do that?

IF is only plpgsql statement - so you cannot to call it from SQL environment.

CREATE FUNCTION is not expensive command, and you can replace existing function by statement

CREATE OR REPLACE FUNCTION ...

This is usual solution. You cannot to use in SQL IF statement ever in PostgreSQL - This T-SQL syntax is not supported.

But lot of DDL statements has integrated IF

DROP TABLE IF EXISTS ...
CREATE TABLE IF NOT EXISTS ...
CREATE OR REPLACE FUNCTION ...

second solution is using SQL command DO - inside is plpgsql code, and you can use conditional statement like IF, or other.

regards

Pavel

Thank you.

Re: IF NOT EXIST

From
Alban Hertroys
Date:
> On 18 Dec 2018, at 7:10, Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, ALL,
> I have a following statement:
>
> IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS
> ns ) CREATE FUNCTION();
>
> Unfortunately trying to execute it thru the ODBC interface with:
>
> ret = SQLExecDirect( m_hstmt, query, SQL_NTS );
>
> gives syntax error near IF.
>
> What is the proper way to do that?

It looks like you’re trying to create a function unless it already exists, but you’re missing several important parts.

Firstly, the statement you’re looking for is CREATE OR REPLACE FUNCTION. Look it up in the docs for the various syntax
optionsand for how to use it, as… 

Secondly, your function doesn’t have a name. A function requires a name, or you wouldn’t ever be able to call it.
You will also have to specify a return type (functions return values) and the language the function is implemented in.
Thedocumentation will show you that there are several options you can provide too. 

And lastly, a function requires an implementation.

Regards,

Alban Hertroys
--
There is always an exception to always.

Re: IF NOT EXIST

From
Adrian Klaver
Date:
On 12/18/18 12:02 AM, Alban Hertroys wrote:
> 
>> On 18 Dec 2018, at 7:10, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, ALL,
>> I have a following statement:
>>
>> IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS
>> ns ) CREATE FUNCTION();
>>
>> Unfortunately trying to execute it thru the ODBC interface with:
>>
>> ret = SQLExecDirect( m_hstmt, query, SQL_NTS );
>>
>> gives syntax error near IF.
>>
>> What is the proper way to do that?
> 
> It looks like you’re trying to create a function unless it already exists, but you’re missing several important
parts.
> 
> Firstly, the statement you’re looking for is CREATE OR REPLACE FUNCTION. Look it up in the docs for the various
syntaxoptions and for how to use it, as…
 
> 
> Secondly, your function doesn’t have a name. A function requires a name, or you wouldn’t ever be able to call it.

With an exception:

https://www.postgresql.org/docs/10/sql-do.html

> You will also have to specify a return type (functions return values) and the language the function is implemented
in.The documentation will show you that there are several options you can provide too.
 
> 
> And lastly, a function requires an implementation.
> 
> Regards,
> 
> Alban Hertroys
> --
> There is always an exception to always.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com