Passing Array of a Composite Type to Stored Procedure

Passing array of a composite type to stored procedure

How you specify your input appears fine, as the same behaviour is observed with row- and array-constructor syntax:

SELECT * FROM do_something( ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[] );

And:

SELECT ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[];

produces:

 '{"(test,11.00)","(test2,22.00)"}'

If you add a:

 RAISE NOTICE '!%!',temp_var;

inside the loop the output is:

NOTICE:  !("(test,11.00)",)!
NOTICE:  !("(test2,22.00)",)!

showing that you're actually getting a tuple with "message" as the tuple text you expected and a null "amount".

So. Why?

It's a bit of a subtle one. You're using:

SELECT unnest(input_array)

which seems to do what you want, right:

regress=>     SELECT unnest( ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[] );
    unnest     
---------------
 (test,11.00)
 (test2,22.00)
(2 rows)

... but actually, it's returning a single column of type composite_type. PL/PgSQL composite type assignment expects one column per type column instead. So the single col is being shoved into 'message' and there is no second col.

Instead, write:

SELECT * FROM unnest(input_array)

to unpack the composite for assignment. Then it works as expected:

regress=> SELECT * FROM do_something( ARRAY[ ROW('test',11), ROW('test2',22) ]::composite_type[] );
 do_something 
--------------
 test
 test2
(2 rows)

If the first field of composite_type were of a non-text type, you'd get an error that was rather more informative about this.

Array of composite type as stored procedure input

The call would be (with two array elements):

SELECT insert_info('{"(Arjay,myEmail@email.com,1234567)"
                    ,"(Bjay,my2Email@email.com,2234567)"}'::info[]);

Or with ARRAY constructor:

SELECT insert_info((ARRAY['(Arjay,myEmail@email.com,1234567)'
                         ,'(Bjay,my2Email@email.com,2234567)'])::info[]);

Or:

SELECT insert_info( ARRAY['(Arjay,myEmail@email.com,1234567)'::info
                         ,'(Bjay,my2Email@email.com,2234567)']);

But the whole operation can be more efficient with plain SQL using unnest():

INSERT INTO info(name, email_add,contact_no)
SELECT * FROM unnest('{"(Arjay,myEmail@email.com,1234567)"
                     , "(Bjay,my2Email@email.com,2234567)"}'::info[]);

You can wrap it into an SQL function if you need it as function call ...

CREATE OR REPLACE FUNCTION insert_info(info_array info[])
  RETURNS void
  LANGUAGE sql AS
$func$
INSERT INTO info(name, email_add,contact_no)
SELECT * FROM unnest($1)
$func$;

Same call.

Your original function would return after the first array element, btw.

Npgsql C# - pass parameters as array of composite type into stored procedure

The official documentation of Npgsql says:

The only way to call a stored procedure is to write your own CALL
my_proc(...) command, without setting CommandBehavior.StoredProcedure.

In your particular case you should modify your code like this:

NpgsqlCommand command = new NpgsqlCommand("call stored_example(:parameters)", conn);
// comment this line command.CommandType = CommandType.StoredProcedure;

Hope it helps bro.

Loop of composite type as stored procedure input PostgreSQL

You get that error message because a parameter (in your case pl) cannot occur in the FROM clause of a query, so PostgreSQL will interpret pl as a table name.

The deeper problem is that you try to assign a set of values to a single variable, which will not work. Your SELECT ... INTO statement will only store the first result in the variable l.

You don't tell us what you really want to achieve, but I can think of two approaches for the problem you show:

1. Handle the query results one by one. PL/pgSQL code would look like that:

   DEFINE
      l leketo;
   BEGIN
      FOR l IN SELECT * FROM getleketo() LOOP
         RAISE NOTICE 'id=%, name=%', l.id, l.name;
      END LOOP;
   END;
   

2. Define getleketo() not as RETURNS SETOF leketo, but as RETURNS refcursor and have it return a cursor for the results. Then you can assign the whole query result to a variable of type refcursor and use this as an argument to the loopleketo function.

   See the documentation for details.

Call postgresql stored procedure with composite type array with uuid as one of the input parameters

Your script problem in iteration over an array - there is an bug in FOR b IN SELECT UNNEST(customer_items). This syntax doesn't do record's unpacking, but FOR IN SELECT statement requires it. You need to write:

FOR b IN SELECT * FROM unnest(customer_items)
LOOP
  ...

or (better)

FOREACH b IN ARRAY customer_items
LOOP
  ...

How can I pass an array of values to my stored procedure?

As Chris pointed, in PostgreSQL it's no problem - any base type (like int, text) has it's own array subtype, and you can also create custom types including composite ones. For example:

CREATE TYPE test as (
    n int4,
    m int4
);

Now you can easily create array of test:

select ARRAY[
    row(1,2)::test,
    row(3,4)::test,
    row(5,6)::test
];

You can write a function that will multiply n*m for each item in array, and return sum of products:

CREATE OR REPLACE FUNCTION test_test(IN work_array test[]) RETURNS INT4 as $$
DECLARE
    i      INT4;
    result INT4 := 0;
BEGIN
    FOR i IN SELECT generate_subscripts( work_array, 1 ) LOOP
        result := result + work_array[i].n * work_array[i].m;
    END LOOP;
    RETURN result;
END;
$$ language plpgsql;

and run it:

# SELECT test_test(
    ARRAY[
        row(1, 2)::test,
        row(3,4)::test,
        row(5,6)::test
    ]
);
 test_test
-----------
        44
(1 row)

Passing user-defined Type Array as input parameter to a function

Your variable assignment is wrong, you need to provide the array index to which you want to assign an element.

When you are calling a function returning a single value, you don't need a SELECT in PL/pgSQL, just assign the result:

do  
$$  
declare  
  v_key key_type[];  
  v_res TEXT;  
begin  
  v_key[1] := ('709R', 'Risk'); -- first array element
  v_key[2] := ('711X', 'Risk2'); -- second array element
  v_res := fn_det(v_key);  
  raise notice '%', v_res;  
end;  
$$  
language plpgsql;

---

Related Topics