postgresql: select returning ARRAY

Question

I have a table as:

CREATE TABLE tbl_temp (id serial, friend_id int, name varchar(32));

I wish I could run the following SQL:

PREPARE x AS SELECT {$1,friend_id} FROM tbl_temp WHERE id = ANY($2);  
EXECUTE x(33, ARRAY[1,2,3,4])

I basically looking for a statement that will return me an array of two ints first of which will be user input and second will be from table column like friend_id.

Is it really possible in PostgreSQL?

The results from SELECT ($1, friend_id) FROM tbl_temp;

EXECUTE x(44);
  row     
--------  
 (44,1)  
 (44,2)  
 (44,3)  
(3 rows)

If I use PQgetvalue(PGres, 0, 0) how will the result look like: {44,45} or like (44,45)?

mu is too short · Accepted Answer · 2016-05-26 03:16:45Z

25

I think you want to use the array constructor syntax:

SELECT ARRAY[$1, friend_id] FROM tbl_temp WHERE id = ANY($2)

edited May 26, 2016 at 3:16

answered May 12, 2011 at 18:16

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Comments

mu is too short · Accepted Answer · 2012-10-01 21:16:41Z

4

i'm not sure i understand what you want...

to return an array, do this.

SELECT (44, "friend_id") FROM "tbl_temp" WHERE id = ANY(ARRAY[1,2,3,4]);

edited Oct 1, 2012 at 21:16

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered May 12, 2011 at 18:05

David Chan

7,5352 gold badges30 silver badges50 bronze badges

1 Comment

Mayank Over a year ago

Thanks for the reponse. I actually was looking for something like SELECT ARRAY[$1, friend_id] FROM tbl_temp WHERE id = ANY($2) ... Got it from mu is too short

Collectives™ on Stack Overflow

postgresql: select returning ARRAY

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related