Pass array literal to PostgreSQL function

Pass array literal to PostgreSQL function

  1. Blog
  2. 15 November 2026
  3. 5 min read

Stefan Bogdanescu

Founder & Senior Architect

Passing Array Literals to PostgreSQL Functions

When a Postgres function needs to filter rows based on an array of string values, the most common pitfall is treating that input as a single scalar value. In your specific case, you have a function get_questions that accepts a text parameter vcode. The goal is to exclude questions whose codes appear in the provided list.

If you simply pass a PHP array to pg_query_params, PostgreSQL receives it as a single string containing the array representation (e.g., {"qcode1","qcode2"}) or, if using older connection styles, a comma-separated string like "qcode1","qcode2". You cannot use this directly inside a standard NOT IN (...) clause because IN expects individual scalar arguments, not a single array object.

The Core Syntax Problem

The query you provided attempts to do the following:

SELECT id, title, code
FROM questions 
WHERE code NOT IN (vcode);

This syntax is fundamentally incorrect for your use case. Even if vcode were passed as a comma-separated string (e.g., 'qcode1','qcode2'), wrapping that string in parentheses like (vcode) does not automatically split the string into individual arguments for the IN operator. PostgreSQL interprets (vcode) as a single column reference or function argument, not as an expansion of values.

To make this work, you must construct the SQL so that the array elements are expanded into distinct arguments for the IN clause.

Solution: Using UNNEST

The cleanest and most robust way to handle this in PostgreSQL is to use the unnest() function. This function takes an array as input and returns a set of rows, one for each element in the array. By wrapping your input parameter with unnest, you transform the single array argument into multiple scalar values that the IN operator can consume naturally.

Here is the corrected SQL logic:

SELECT id, title, code
FROM questions 
WHERE code NOT IN (UNNEST(vcode));

This approach works regardless of how the array is passed to the function, provided the type matches. Since your parameter vcode is defined as text, PostgreSQL will expect a single text argument that represents the array.

Implementing in PHP with pg_query_params

Since you are using PHP 5.3.6 and pg_query_params, you need to ensure the data passed from PHP matches what the SQL function expects. In this version of Postgres, passing an actual PHP array directly to pg_query_params usually requires the backend function to accept a specific format or for the driver to handle the conversion.

However, if your function signature defines vcode as text, you generally need to pass the array values as a single string that represents the array, or use pg_query_params with an array of strings and let Postgres handle the type coercion in the function definition.

If you want to pass a standard PHP array from your application code into a function expecting a text array, you have two main paths depending on how strictly you define the function:

  1. Pass as a Comma-Separated String: If your function expects a text string like "qcode1","qcode2", you must format the PHP array accordingly before passing it to pg_query_params.

    $codes = ['qcode1', 'qcode2'];
    // Format as a PostgreSQL array literal string
    $paramString = "'" . implode('","', $codes) . "'"; 
    // Result: 'qcode1","qcode2' -> actually needs quotes for strings inside
    // Correct formatting for text array passed as single parameter:
    $formattedCodes = "'" . implode "', '", $codes) . "'";
    
    $sql = "SELECT * FROM get_questions($formattedCodes)";
    

    Note: This manual string interpolation is error-prone and bypasses the safety of pg_query_params.

  2. Pass as an Array of Strings: The safer approach with pg_query_params is to pass the individual elements. However, this requires your Postgres function signature to accept an array type (e.g., text[]) rather than a single text.

    If you are constrained to keeping the function parameter as text, you must construct the array literal string carefully or use a different data structure.

    Here is the recommended pattern for PHP 5.3.6 using pg_query_params with an array of values, assuming you can adjust the function to accept vcode text[]:

    $codes = ['qcode1', 'qcode2'];
    
    // pg_query_params handles the array splitting automatically if the placeholder is an array
    // But your function definition currently says RETURN return_value and input vcode text.
    // If you change function to: CREATE FUNCTION get_questions(vcode text[]) ...
    
    $sql = "SELECT id, title FROM questions WHERE code NOT IN (UNNEST($1))";
    $params = [$codes]; // Pass the PHP array directly
    
    $result = pg_query_params($conn, $sql, $params);
    

Important Trade-offs and Pitfalls

Type Consistency: The most critical requirement is that the type passed to unnest must match the type of the column being compared. If your code column is text, vcode must be treated as an array of text. Passing a numeric array to a text comparison will fail without explicit casting, which can lead to subtle bugs if not handled explicitly.

Function Signature Mismatch: Your current function definition uses vcode text. If you pass an actual PHP array via pg_query_params, PostgreSQL expects the corresponding SQL parameter placeholder $1 to map to a specific type. If your function is defined as taking a single text string, passing an array of strings directly might cause a type mismatch error unless the driver or function handles the conversion internally. The most reliable fix is often to update the function signature to accept vcode text[] and rely on UNNEST.

Performance Considerations: Using NOT IN (UNNEST(...)) creates an index scan if there is a unique index on the code column, but it can still be slower than a direct lookup for very large arrays. If performance becomes an issue with thousands of codes, consider using EXCEPT or a join against a temporary table constructed from the array, though UNNEST remains the standard idiomatic solution for readability and maintainability.

For more complex data handling patterns in PHP applications, you might find additional resources at https://laravelcompany.com regarding efficient database interaction strategies.

Our network