复合类型数组作为存储过程输入

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

我通过传递一个类型数组使用plpgsql创建一个存储过程,并在该过程中执行一个循环,以便我可以插入每个信息类型

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','myEmail@email.com','1234567')]
);

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

但是PostgreSQL说它是一个记录[]而我还没有测试过Loop部分......

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!

我在这个链接中发现了一个类似的问题:使用%TYPE在PostgreSQL中声明复合类型的变量,但它没有使用数组。如果这只是一个重复的问题,也许你们可以指出我正确的方向!

1 个解决方案

#1

The call would be:

电话会是:

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

Or with ARRAY constructor:

或者使用ARRAY构造函数:

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 could be more efficient with plain SQL using unnest():

但使用unexst()的纯SQL可以提高整个操作的效率:

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 this into an SQL function if you need it as function call ...

如果你需要它作为函数调用,你可以将它包装成一个SQL函数...

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

Same call.

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

您的原始函数将在第一个数组元素btw之后返回。

#1