Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 620
Mayank Avatar asked May 12 '11 17:05

Mayank


People also ask

How do I select an array in PostgreSQL?

PostgreSQL allows us to define a table column as an array type. The array must be of a valid data type such as integer, character, or user-defined types. To insert values into an array column, we use the ARRAY constructor.

What is text [] in Postgres?

PostgreSQL supports a character data type called TEXT. This data type is used to store character of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same.

Can Postgres store arrays?

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.


2 Answers

I think you want to use the array constructor syntax:

SELECT ARRAY[$1, friend_id] FROM tbl_temp WHERE id = ANY($2)
like image 61
mu is too short Avatar answered Oct 04 '22 17:10

mu is too short


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]);
like image 31
David Chan Avatar answered Oct 04 '22 16:10

David Chan