How to create a parameter in a PostgreSQL function with multiple values in it?

Question

I am working on a function in PostgreSQL where I need to create a parameter and pass multiple values within it.

For example, I have a table named group which has 2 fields: group_ck and groupid. groupid has values like:

(abc,def,lsm,fed,sedd,erer,dlojn)

and group_ck is a sequence no.

Now I need to build a function and pass groupid values. I know in sql we can define:

@group in=('abc','def','lsm','fed','sedd','erer','dlojn')

But in PostgreSQL, it doesn't work like this way. Can any one help me and provide an example on how I can pass multiple values in a parameter?

Pass it as an array?: postgresql.org/docs/current/static/arrays.html — Nick
– Nick, Commented Sep 24, 2018 at 2:23

S-Man · Accepted Answer · 2018-09-24 04:34:08Z

2

You could take an array as parameter:

CREATE FUNCTION my_function(params text[])

Then you are able to check it inside the function with something like

my_value = ANY(params)

(params should be your groupid)

answered Sep 24, 2018 at 4:34

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gowtami Over a year ago

thanks , so here is what i did and its throwing an error.DROP FUNCTION IF EXISTS abc (CHARACTER); CREATE OR REPLACE FUNCTION abc (,p_grgr_id CHARACTER) RETURNS VOID AS $$ declare l_GRGR_ID CHARACTER (100); if coalesce(trim(p_grgr_id), '') = '' THE l_grgr_id= grgr_id from group where grgr_id like 'PEND%' and grgr_id= all(array[PENDD,PENDL,PENDC,PENDM,PENDF]); else l_grgr_id:= p_grgr_id; end if; raise info 'l_grgr_id: %', l_grgr_id; error says :column "pendd" does not exist

S-Man Over a year ago

In your comment are many syntax errors. Please use a fiddle to demonstrate your problem: dbfiddle.uk

Collectives™ on Stack Overflow

How to create a parameter in a PostgreSQL function with multiple values in it?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related