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

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 [].

CodePudding user response：

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[]

CodePudding user response：

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'

CodePudding user response：

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