How can I use multiple values in SQL variable in PostgresSQL/plpgsql?

Question

I want to declare variable as v_nk varchar;

then assign the variable v_nk := ''S001234','S23401','S34509','S9900'';

and use this variable in select query

select * from mytable where nk in (v_nk);

I want to use this in one of my program(plpgsql) can you please tell me how I should use it?

Roman Tkachuk · Accepted Answer · 2017-01-30 20:04:22Z

3

You can use array :

v_nk := ARRAY [ 'S001234'::text ,'S23401','S34509','S9900'];

OR

v_nk := '{S001234,S23401,S34509,S9900}'::text[];

and use this variable in select query:

select * from mytable where nk = ANY (v_nk);

edited Jan 30, 2017 at 20:04

user330315

answered Jan 30, 2017 at 19:55

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

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

6 Comments

Madhu Over a year ago

Hi @RomanTkachuk I tried this it didn't work. I received an error.

Roman Tkachuk Over a year ago

Are you declare v_nk AS text[]?

Madhu Over a year ago

Hey @RomanTkachuk ......When I used v_nk : ARRAY ['S001234'::text,'S9900']; I got this error - Array value must start with "{" or dimension information. Then I used 2nd solution It transforms into run time as where nk = ANY("{S001234,S23401,S34509,S9900}") And gives error for "{" bracket

Roman Tkachuk Over a year ago

Can you try: SELECT ARRAY ['S001234'::text,'S9900'];

Roman Tkachuk Over a year ago

And = ANY('{S001234,S23401,S34509,S9900}'::text[]) - Please use ' NOT "

|

sia · Accepted Answer · 2017-01-30 19:56:28Z

1

you can define array instead of variable like this:

DECLARE nk_array    VARCHAR(30)[] = '{'S001234','S23401','S34509','S9900' }';
select * from mytable where nk in nk_array;

answered Jan 30, 2017 at 19:56

sia

5891 gold badge9 silver badges24 bronze badges

2 Comments

Madhu Over a year ago

Hey @sia I tried this as well Its giving me error.. Doesn't work

Laurenz Albe Over a year ago

You messed up the quotes.

Collectives™ on Stack Overflow

How can I use multiple values in SQL variable in PostgresSQL/plpgsql?

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related