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!
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('.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With