CODEWITHSUNDEEP
postgresql
Ugy Astro
Ugy Astro

Reputation: 417

PostgreSQL : Cast Data Type from Text to Bigint when using WHERE IN

I've problem when try to cast data type from TEXT to BIGINT when using WHERE IN on PostgreSQL in procedure. This always gives

operator does not exist: bigint = text. Try cast the variable in the query.

But still get the same notice. This is example query:

    DECLARE

     -- $1 params text

    BEGIN
      SELECT * FROM table_a where 
      colId IN($1); // notice is here, colId is bigint
    END 

    /*Call the procedure*/

    SELECT my_function('1,2,3,4,5');

How do we cast the variable? Thanks!

Upvotes: 0

Views: 1448

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45930

Using strings for id list is wrong design. You can use a arrays in PostgreSQL.

For example

CREATE OR REPLACE FUNCTION foo(VARIADIC ids int[])
RETURNS SETOF table_a AS $$
SELECT * FROM table_a WHERE id = ANY($1)
$$ LANGUAGE sql;

SELECT foo(1,2,3);

But, usually wrapping simple SQL to functions looks like broken design. Procedures should not to replace views.

Upvotes: 2

Related Questions