Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Array of composite type as stored procedure input

I am creating a stored procedure using plpgsql by passing a type array and do a loop inside the procedure so that I can insert each info type

CREATE TYPE info AS(
    name  varchar,
    email_add  varchar,
    contact_no  varchar 
);

CREATE OR REPLACE FUNCTION insert_info(
    info_array  info[]
) RETURNS varchar AS $$
    DECLARE
        info_element  info;
    BEGIN
        FOREACH info_element IN ARRAY info_array
        LOOP
            INSERT INTO info_table(
                name,
                email_add,
                contact_no
            ) VALUES(
                info_element.name,
                info_element.email_add,
                info_element.contact_no
            );
        END LOOP;
        RETURN 'OK';
    END;
$$ LANGUAGE plpgsql;

The problem is I don't know how to use the function with the array input. I did some experiments (with just some stupid inputs):

SELECT insert_info(
    ARRAY[('Arjay','[email protected]','1234567')]
);

But PostgreSQL says that it is a record[] and I still haven't tested the Loop part ...

I have found a similar problem in this link:
Declare variable of composite type in PostgreSQL using %TYPE
but it did not use arrays. If this is just a duplicate question maybe you guys can point me in the right direction!

like image 804
megamoth Avatar asked Oct 08 '13 07:10

megamoth


People also ask

How do I pass a list as parameter in SQL stored procedure?

CREATE FUNCTION dbo. SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>'). query('.

What is Plpgsql used for?

With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings because you don't have the whole client/server communication overhead.

How do I pass a list as parameter in mysql stored procedure?

After the name of the procedure, the list of parameters must be specified in the parenthesis. The parameter list must be comma-separated. The SQL Queries and code must be written between BEGIN and END keywords.

What is composite type in SQL?

A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type.


1 Answers

The call would be (with two array elements):

SELECT insert_info('{"(Arjay,[email protected],1234567)"
                    ,"(Bjay,[email protected],2234567)"}'::info[]);

Or with ARRAY constructor:

SELECT insert_info((ARRAY['(Arjay,[email protected],1234567)'
                         ,'(Bjay,[email protected],2234567)'])::info[]);

Or:

SELECT insert_info( ARRAY['(Arjay,[email protected],1234567)'::info
                         ,'(Bjay,[email protected],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,[email protected],1234567)"
                     , "(Bjay,[email protected],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.

like image 192
Erwin Brandstetter Avatar answered Oct 13 '22 06:10

Erwin Brandstetter