Geek asks and answers

PostgreSQL: A PREPARED statement with ANY ($ 1)

I want to create a PREPARED STATEMENT in which they enter some integers whose account is unknown. What I'm trying to do is

PREPARE prep_select_data AS SELECT ARRAY[$1, id], $1, * FROM tbl_data WHERE id = ANY($1);

The error I get is:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: ...ELECT ARRAY[$1, id], $1, * FROM tbl_data WHERE id = ANY($1);
                                                             ^

I can not understand the problem.

Please inform.

Regards,
Mayank

+3
source share
1 answer

As the error says, you need to specify your $1as array (integers):

-- DEALLOCATE prep_select_data;
PREPARE prep_select_data (int[]) AS 
SELECT
    $1 || ARRAY[id], $1, *
FROM
    tbl_data 
WHERE
    id = ANY($1);

I changed ARRAY[$1, id]to $1 || ARRAY[id], so the result looks like this:

EXECUTE prep_select_data ('{1, 2}');
 ?column? | ?column? | id | value 
----------+----------+----+-------
 {1,2,1}  | {1,2}    |  1 | aaa
 {1,2,2}  | {1,2}    |  2 | bbb

This is because (from doc ):

multidimensional arrays must have matching extents for each dimension. a mismatch causes an error report.

+4
source

More articles:


All Articles