CODEWITHSUNDEEP
sqlarrayspostgresql
Mayank
Mayank

Reputation: 5738

postgresql: select returning ARRAY

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)?

Upvotes: 14

Views: 55827

Answers (2)

mu is too short
mu is too short

Reputation: 434965

I think you want to use the array constructor syntax:

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

Upvotes: 25

David Chan
David Chan

Reputation: 7505

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]);

Upvotes: 4

Related Questions