oracle - How to access and query objects passed as parameter to a procedure while converting from Oracle to postgresql

Question

I have a procedure in Oracle that I need to convert to Postgresql and need help on it. It paases a collection of objects in a procedure.The procedure then checks if each object is present in a database table or not and if present it gives a message that , that specific element is found/present. if some element that is paassed to the procedure is not present in the table, the procedure just doesnt do anything. I have to write equivalent of that in postgresql. I think the heart of the issue is this statement:

SELECT COUNT (*)
        INTO v_cnt
        FROM **TABLE (p_cust_tab_type_i)** pt
       WHERE pt.ssn = cc.ssn;

In Oracle a collection can be treated as a table and one can query it but I dont know how to do that in postgresql. The code to create the table, add data, create the procedure, call the procedure by passing the collection (3 objects) and output of that is posted below. Can someone suggest how this can be done in postgresql?

Following the oracle related code and details:

--create table
    create table temp_n_tab1
    (ssn    number,
           fname  varchar2(20),
           lname  varchar2(20),
           items  varchar2(100));
    /

    --add data
    insert into temp_n_tab1 values (1,'f1','l1','i1');
    --SKIP no. ssn no. 2 intentionally..
    insert into temp_n_tab1 values (3,'f3','l3','i3');
    insert into temp_n_tab1 values (4,'f4','l4','i4');
    insert into temp_n_tab1 values (5,'f5','l5','i5');
    insert into temp_n_tab1 values (6,'f6','l6','i6');
    commit;

    --create procedure 
SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE temp_n_proc (
   p_cust_tab_type_i   IN temp_n_customer_tab_type)
IS
   t_cust_tab_type_i   temp_n_customer_tab_type;
   v_cnt               NUMBER;
   v_ssn               temp_n_tab1.ssn%TYPE;


   CURSOR c
   IS
        SELECT ssn
          FROM temp_n_tab1
      ORDER BY 1;
BEGIN
   --t_cust_tab_type_i := p_cust_tab_type_i();

   FOR cc IN c
   LOOP
      SELECT COUNT (*)
        INTO v_cnt
        FROM TABLE (p_cust_tab_type_i) pt
       WHERE pt.ssn = cc.ssn;

      IF (v_cnt > 0)
      THEN
         DBMS_OUTPUT.put_line (
               'The array element '
            || TO_CHAR (cc.ssn)
            || ' exists in the table.');

      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

--caller proc
SET SERVEROUTPUT ON

declare
            array temp_n_customer_tab_type := temp_n_customer_tab_type();
    begin
            for i in 1 .. 3
            loop
                    array.extend;
                    array(i) := temp_n_cust_header_type( i, 'name ' || i, 'lname ' || i,i*i*i*i );
            end loop;
            temp_n_proc( array );
   end;
   /

caller proc output:

The array element 1 exists in the table.
The array element 3 exists in the table.

标签： oraclepostgresqlstored-procedures

Answer 1

When you create a table in Postgres, a type with the same name is also created. So you can simply pass an array of the table's type as a parameter to the function.

Inside the function you can then use unnest() to treat the array like a table.

The following is the closest match to your original Oracle code:

create function temp_n_proc(p_cust_tab_type_i temp_n_tab1[])
  returns void
as
$$
declare
   l_rec record;
   l_msg text;
   l_count integer;
BEGIN
   for l_rec in select t1.ssn
                from temp_n_tab1 t1
   loop
     select count(*)
       into l_count
     from unnest(p_cust_tab_type_i) as t
     where t.ssn = l_rec.ssn; 

     if l_count > 0 then
       raise notice 'The array element % exist in the table', l_rec.ssn;
     end if;

   end loop;

END;
$$
language plpgsql;

The row-by-row processing is not a good idea to begin with (neither in Postgres, nor in Oracle). It would be a lot more efficient to get the existing elements in a single query:

create function temp_n_proc(p_cust_tab_type_i temp_n_tab1[])
  returns void
as
$$
declare
   l_rec record;
   l_msg text;
BEGIN
   for l_rec in select t1.ssn
                from temp_n_tab1 t1
                where t1.ssn in (select t.ssn 
                                 from unnest(p_cust_tab_type_i) as t) 
   loop
     raise notice 'The array element % exist in the table', l_rec.ssn;
   end loop;

   return;
END;
$$
language plpgsql;

You can call the function like this:

select temp_n_proc(array[row(1,'f1','l1','i1'), 
                         row(2,'f2','l2','i2'), 
                         row(3,'f3','l3','i3')
                        ]::temp_n_tab1[]);

---

However a more "Postgres" like and much more efficient way would be to not use PL/pgSQL for this, but create a simple SQL function that returns the messages as a result:

create or replace function temp_n_proc(p_cust_tab_type_i temp_n_tab1[])
  returns table(message text)
as
$$
  select format('The array element %s exist in the table', t1.ssn)
  from temp_n_tab1 t1
  where t1.ssn in (select t.ssn 
                   from unnest(p_cust_tab_type_i) as t) 
$$
language sql;

This returns the output of the function as a result rather than using the clumsy raise notice.

You can use it like this:

select *
from temp_n_proc(array[row(1,'f1','l1','i1'), 
                       row(2,'f2','l2','i2'), 
                       row(3,'f3','l3','i3')
                      ]::temp_n_tab1[]);