1

code in oracle

parameter info = in iv_column_1, in iv_column_2, inout iv_msg

begin 
  update main_table set column_1 = iv_column_1 where column_2 = 
  iv_colmun_2;
  if sqlcode <> 0 then
    iv_msg := 'main_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;
  insert into history_table (column_1 , column_2) values (iv_column_1, 
  iv_column_2);
    if sqlcode <> 0 then
    iv_msg := 'history_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;

code in postgresql

parameter info = in iv_column_1, in iv_column_2, inout iv_msg

begin 
  update main_table set column_1 = iv_column_1 where column_2 = 
  iv_colmun_2;
  if sqlstate <> 0 then
    iv_msg := 'main_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;
  insert into history_table (column_1 , column_2) values (iv_column_1, 
  iv_column_2);
    if sqlstate <> 0 then
    iv_msg := 'history_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;

error text :

SQL Error [42703]: ERROR: column "sqlstate" does not exist

ksm5654f
  • 11
  • 2
  • Looks like you want some exception handling: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING – Frank Heikens Mar 15 '23 at 07:06
  • [get diagnostics](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS) maybe? –  Mar 15 '23 at 09:13

1 Answers1

0

The Postgres equivalent of Oracle's sqlcode 0 is the global variable FOUND (see Basic Statements:Table 43.1. Available Diagnostics Items. It is set by each of the following types of statements:

UPDATE, INSERT, DELETE, and MERGE statements set FOUND true if at least one row is affected, false if no row is affected.

So your code becomes:

begin 
  update main_table set column_1 = iv_column_1 where column_2 = 
  iv_colmun_2;
  if not FOUND then
    iv_msg := E'main_table error \n ' ||SQLERRM;
    return;
  end if;
  insert into history_table (column_1 , column_2) values (iv_column_1, 
  iv_column_2);
  if not FOUND then
    iv_msg := E'history_table error \n ' ||SQLERRM;
    return;
  end if;

Assuming this is just part of a larger block. If an error occurs on the insert Postgres will most likely throw an exception, in which case a EXCEPTION segment of the block is required. As others have indicated.
NOTE: Not tested as no data provided.

Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Is it possible to apply 'if not FOUND then' in an insert, update, delete statement? – ksm5654f Mar 16 '23 at 01:57
  • NO! First, control statements *if, for, while, ...* **do not exist** in SQL, in Postgres those are Plpgsql statements not SQL. While SQL is easily integrate Plpgsql they are not the same language. Further FOUND is set at the completion of the SQL statements (insert,update, delete, merge, select). FOUND itself does not exist in SQL and in only accessible within Plpgsql. – Belayer Mar 16 '23 at 05:04