PostgreSQL: PL/Python function not taking ARRAY input parameter

Question

Objective:

• Creating a full text search engine using PostgreSQL FTS.

Populating tsvector:

I have a tsvector column. To populate tsvector column, I am executing a trigger which calls a procedure which executes a custom PL/Python function.

Steps followed:

Trigger and Procedure function

Its something similar to https://www.postgresql.org/docs/10/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS trigger which calls messages_trigger procedure(written in plpgsql).

But instead of coalesce(new.title,'') and coalesce(new.body,''), I am calling a UDF which has ARRAY of text as an input parameter.

custom_udf(p_arr ARRAY)

Error:

During ingestion of data, it throws the error:

psycopg2.errors.UndefinedObject: type p_arr[] does not exist

Question:

Is ARRAY type parameter not allowed for Pl/Python function?

To by pass the issue, I am doing a comma separated join of list elements and passing that to the custom_udf. And inside custom_udf, I am using comma delimiter split to get back the list.

Answer 1

The syntax you used:

CREATE FUNCTION custom_udf(p_arr ARRAY) RETURNS ...;

means the same as

CREATE FUNCTION custom_udf(p_arr[]) RETURNS ...;

That is, a function with one unnamed parameter that has the data type “array of p_arr”. So PostgreSQL expects p_arr to be a data type, which explains the error message.

There is no “array” data type in PostgreSQL, you always have to name the element type, for example integer ARRAY or, equivalently, integer[].

So, assuming that you want an array of strings, your definition should look like

CREATE FUNCTION custom_udf(p_arr text[]) RETURNS ...;