Question 89977 in PostgreSQL Question posted in
The official documentation can be found here.

How to filter a PostgreSQL table by passing an array of strings in the function

I want to pass an array like [group1, group2, group3] and then filter the Postgres table column groups.

Create or replace function funname(groups text[])
Select *
From tableName
Where groupscolumn @> groups

Also kindly write the function for the same, getting an error while defining character varying [].

Tags:

3

Answers


  1. 0

    You can use the unnest function to convert an array in ‘table’ output and then filter the value that you need, for example:

    SELECT * FROM (
SELECT unnest (ARRAY['group1','group2','group3']) AS arr
) AS sub WHERE arr ='group3'
    Login or Signup to reply.
  2. 0

    It’s unclear to me what you want, but maybe you are looking for the ANY operator?

    select * 
from some_unknown_table
where group_column = any(array_parameter);

    This requires that the data type of the group_column and the one of the parameter match. E.g. if group_column is text or varchar the parameter needs to be declare as text[]. If group_column is e.g. an integer, the parameter needs to be declared as integer[]

    Login or Signup to reply.
  3. 0

    You can use join for groups array and SELECT WHERE group IN groups for filter:

    import psycopg2


def filter_groups(group_names):


try:
    # connect to BD
    connection = psycopg2.connect(
        host="host",
        user="user",
        password="pass",
        database="db_name"
    )
except Exception as _ex:
    print("[INFO] Connection error", _ex)
try:
    with connection.cursor() as cursor:
        placeholders = ','.join(['%s'] * len(group_names))
        rsql = f"SELECT * FROM table_name WHERE groups IN ({placeholders})"
        cursor.execute(rsql, group_names)
        rows = cursor.fetchall()
        cursor.close()
except Exception as _ex:
    print("[INFO] Error while working with PostgreSQL", _ex)

if connection:
    connection.close()
    print("[INFO] PostgreSQL connection closed.")
return rows
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search