How to pass String Array as an input parameter in PostgreSQL

String Array as an input parameter in PostgreSQL

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a demonstration How to pass the string array as an input parameter in stored function of PostgreSQL.

We already used a different approach to pass multiple values as a single input parameter like comma separated, XML Parameter, Table Type.
You can also find one more type which is an array of PostgreSQL.

I created a stored function for this demonstration.
In the below, the stored function I declared one input parameter as a string array and using for..loop I am fetching record from this array and storing into one temporary table.

CREATE OR REPLACE FUNCTION fn_PostgresArray

(

ArrayText character varying[]

)

RETURNS TABLE (outArrayTextData CHARACTER VARYING(250)) AS

$BODY$

DECLARE

Counter INT = 0 ;

BEGIN

CREATE TEMP TABLE ArrayData(ArrayTextData CHARACTER VARYING(250));

FOR Counter in array_lower(ArrayText, 1) .. array_upper(ArrayText, 1)

LOOP

INSERT INTO ArrayData VALUES(ArrayText[Counter]);

END LOOP;

RETURN QUERY

SELECT ArrayTextData

FROM ArrayData;

DISCARD TEMP;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

Now let’s call the above function by passing a string array.

1	SELECT *FROM fn_PostgresArray(ARRAY['Apple', 'Orange', 'Mango']);

The result is:

Sep 21, 2015Anvesh Patel

Comments: 5

Pramod

June 27, 2018 at 1:17 pm

I have created a type which and trying to pass it as argument in procedure, but getting an error as ‘type XXX does not exists’

Anvesh Patel
June 28, 2018 at 5:45 pm
Did you create your own datatype?
If yes then give permission to your user.

suresh

October 8, 2018 at 11:54 am

HI Anvesh,

I am new in postgresql. I am learning the through your blog,
I want to implement the table values parameter in stored function in postgresql.
In MS SQL , I can do the below script

Create type tbl as table(sno int,name varchar(100))
go
declare @tt as tbl
insert @tt(sno,name) values (1,’XXX’)
insert @tt(sno,name) values (2,’YYY’)
insert @tt(sno,name) values (3,’ZZZ’)
create proc pgetlist (@tt tbl)
as
begin
select * from @tt
end

Please provide the sample script how can implement the same in postgresql.
Please help me.

Anvesh Patel
October 9, 2018 at 7:29 pm
I think, PostgreSQL doesn’t support table value parameter. But using array parameter, you can do similar like table value.

Kiran

March 23, 2019 at 5:25 am

How to implement table value parameter in postgresql like sql server

String Array as an input parameter in PostgreSQL

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !