Using Postgres Connector, is string_to_array() supported?

Google Cloud Integration Services
,
1

Hello

I’m using the Postgres connector and have a requirement to run a query to match a list of values.

I receive a comma-separated list of values (example: value1,value2) and my simplified query in Application Integration is the following:

SELECT * FROM test_table

WHERE process = ?

AND name = ?

AND status = ANY(string_to_array(?, ‘,’))

When running this i get the error:
*[{“@typetypetypetype”:“type.googleapis.com/google.rpc.ErrorInfo",“metadata”:{“cause”:"Server error [SQL state: 42601]: syntax error at or near \“AND\” (Character 50)\n”,“connection_type”:“Postgresql”,“query_string”:"SELECT * FROM agent_status \r\nWHERE process = ? \r\nAND available_day_id = ? \r\nAND agent_status = ANY(string_to_array(?, ‘,’))
*
Already tried building a fixed query to see if it was an issue with data types of the query parameters but i can’t seem to be able to run this query successfuly

Any idea if the function string_to_array() is actually supported using the connector?
Do i have other alternatives other than building a string with all the values and using IN?

thank you!

2

Hey @diogoalfarelos,

We’ve seen that your question hasn’t gotten a response yet. We’ll keep checking in on this thread and encourage other members to share their thoughts.

3

We are able to replicate the error in our environment.

Technically, string_to_array() is supported in PostgreSQL, but the connector’s parameterization layer may not support combining it with parameters directly, specially inside complex expressions.

The query used by @diogoalfarelos breaks because the connector sends the third parameter as a string literal, not as an expression, so:

status = ANY(string_to_array(‘value1,value2’, ‘,’))

is legal SQL, but the connector might escape or mishandle the syntax before sending it, which results in:

syntax error at or near “AND”

Work around

  1. Create a function to achieve the same goal as the query(the function in Postgresql is similar to stored procedure but with few differences). It can be created using the executeCustomQuery action.

  2. Call the function and provide the input parameters in data mapping.

I created one sample entity and a function mentioned below to test the work around steps.

The below query is translated to the function->

SELECT * FROM Customers

WHERE Country = ?

AND City = ANY(string_to_array(?, ‘,’))

Sample entity

==========

CREATE TABLE Customers (

CustomerID SERIAL PRIMARY KEY,

CustomerName VARCHAR(255),

ContactName VARCHAR(255),

Address VARCHAR(255),

City VARCHAR(255),

PostalCode VARCHAR(20),

Country VARCHAR(50),

Phone VARCHAR(20)

);

Function definition

==============

CREATE OR REPLACE FUNCTION get_customers_by_cities(

p_country TEXT,

p_cities_csv TEXT

)

RETURNS TABLE (

CustomerID INT,

CustomerName VARCHAR,

Address VARCHAR,

City VARCHAR,

PostalCode VARCHAR,

Country VARCHAR,

Phone VARCHAR

) AS $$

BEGIN

RETURN QUERY

SELECT

c.CustomerID,

c.CustomerName,

c.Address,

c.City,

c.PostalCode,

c.Country,

c.Phone

FROM Customers c

WHERE c.Country = p_country

AND c.City = ANY(string_to_array(p_cities_csv, ','));

END;

$$ LANGUAGE plpgsql;

1 Like
5

thank you for the reply

using the ExecuteCustomQUery action from the Postgresql Connector can i declare the function and the call statement in the same execution?

tried adding both but getting the error:
error [SQL state: 42601]: cannot insert multiple commands into a prepared statement

when i try to add just the function i get another error related to permissions (some i have to solve regarding my user):
[SQL state: 42501]: permission denied for schema public

For reference the code i’m trying to run is:

CREATE OR REPLACE FUNCTION filter_agent_status_by_processes(
p_processes_list TEXT,
p_end_timestamp TIMESTAMP WITH TIME ZONE,
p_start_timestamp TIMESTAMP WITH TIME ZONE
)
RETURNS SETOF agent_status
LANGUAGE sql
AS $$
SELECT *
FROM agent_status
WHERE process = ANY (string_to_array(p_processes_list, ‘,’))
AND ingestion_timestamp <= p_end_timestamp
AND ingestion_timestamp >= p_start_timestamp;
$$;

SELECT *
FROM filter_agent_status_by_processes(
‘ProcessA,ProcessB,ProcessC’,
‘2025-10-15 12:00:00+00’,
‘2025-10-10 00:00:00+00’
);