Postgres Pro
Downloads
Home   >   mailing lists

Thread: execute if statement

execute if statement

From
Peter Kroon
Date:
How can I achieve this?

EXECUTE '

if 1=1 then
raise notice ''%'', ''notice has been raised...'';
end if;

';

Re: execute if statement

From
Raymond O'Donnell
Date:
On 01/12/2012 19:11, Peter Kroon wrote:
> How can I achieve this?
>
> EXECUTE '
>
> if 1=1 then
> raise notice ''%'', ''notice has been raised...'';
> end if;
>
> ';

I think EXECUTE used like this is available only in a function:

  create or replace function my_function()
  returns void
  as
  $$
  begin
    execute ....
  end;
  $$
  language plpgsql;

In "ordinary" SQL, EXECUTE executes a prepared statement.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: execute if statement

From
Peter Kroon
Date:
Mmmmm.......

How do I execute dynamic sql that starts with an if statement.
I'm converting mssql code to pgsql.


2012/12/1 Raymond O'Donnell <rod@iol.ie>
On 01/12/2012 19:11, Peter Kroon wrote:
> How can I achieve this?
>
> EXECUTE '
>
> if 1=1 then
> raise notice ''%'', ''notice has been raised...'';
> end if;
>
> ';

I think EXECUTE used like this is available only in a function:

  create or replace function my_function()
  returns void
  as
  $$
  begin
    execute ....
  end;
  $$
  language plpgsql;

In "ordinary" SQL, EXECUTE executes a prepared statement.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: execute if statement

From
Peter Kroon
Date:
To give a better idea:

DO $$
DECLARE v_string text = 'raise notice ''%'', ''this could be sql with if statement...'';';
BEGIN
if 1=1 then
raise notice '%', ' first notice has been raised...';
end if;
EXECUTE v_string;--this fails
END;
$$
LANGUAGE plpgsql;

I do not wish to create a function for each query I have.


2012/12/1 Peter Kroon <plakroon@gmail.com>
Mmmmm.......

How do I execute dynamic sql that starts with an if statement.
I'm converting mssql code to pgsql.


2012/12/1 Raymond O'Donnell <rod@iol.ie>
On 01/12/2012 19:11, Peter Kroon wrote:
> How can I achieve this?
>
> EXECUTE '
>
> if 1=1 then
> raise notice ''%'', ''notice has been raised...'';
> end if;
>
> ';

I think EXECUTE used like this is available only in a function:

  create or replace function my_function()
  returns void
  as
  $$
  begin
    execute ....
  end;
  $$
  language plpgsql;

In "ordinary" SQL, EXECUTE executes a prepared statement.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie


Re: execute if statement

From
Raymond O'Donnell
Date:
On 01/12/2012 19:44, Peter Kroon wrote:
> Mmmmm.......
>
> How do I execute dynamic sql that starts with an if statement.
> I'm converting mssql code to pgsql.
>

Without trying it, I'd guess that you need to have the IF outside the
dynamic code, something like this:

  if .... then
    execute '.....';
  end if;

If you show what you're trying to achieve, someone might be able to
suggest something better.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: execute if statement

From
Jasen Betts
Date:
On 2012-12-01, Peter Kroon <plakroon@gmail.com> wrote:
> --f46d043be1f4bd2dec04cfcfbd6a
> Content-Type: text/plain; charset=ISO-8859-1
>
> Mmmmm.......
>
> How do I execute dynamic sql that starts with an if statement.

"if" is not SQL.

> I'm converting mssql code to pgsql.

probably best to rewrite at a higher level.

--
⚂⚃ 100% natural

Re: execute if statement

From
John R Pierce
Date:
On 12/1/2012 11:59 AM, Peter Kroon wrote:
>
> I do not wish to create a function for each query I have.
>

query's aren't IF statements.

SELECT stuff FROM table WHERE conditions ....;    <=  thats a query.