Full example of filtering PostgreSQL queries in node-postgres using `= ANY(...)`

postgres-equals-any.js

#!/usr/bin/env node

var util = require('util');

var async = require('async');
var pg = require('pg');

//---------------------------------------------------------------------------------------------------------------------

var connection = {
    host: 'server-hostname',
    port: 5432,
    database: 'postgres',
    user: 'postgres',
    password: '',
};

var client;
var done;

//---------------------------------------------------------------------------------------------------------------------

// Main logic path. (see https://github.com/caolan/async#waterfall)
async.waterfall(
        [
            connect,
            beginTransaction,
            setupTempTable,
            runQuery,
            printResults,
            rollback,
        ],
        shutdown
        );

//---------------------------------------------------------------------------------------------------------------------

function connect(callback)
{
    pg.connect(connection, function onConnect(error, client_, done_)
    {
        client = client_;
        done = done_;

        if(error)
        {
            console.error("Error setting up database: %s", error.stack || error.toString());
        } // end if

        // Manually pass a second 'null' argument, so this adheres to the same interface as pg.query.
        callback(error, null);
    }); // end onConnect
} // end connect

// Since pg.query passes its callback (error, results), all of the following callbacks take a 'results' argument; most
// of them ignore it.

function beginTransaction(_, callback) { client.query("BEGIN TRANSACTION", callback); }
function commit(_, callback) { client.query("COMMIT", callback); }
function rollback(_, callback) { client.query("ROLLBACK", callback); }

function setupTempTable(_, callback)
{
    client.query({
        text: "CREATE LOCAL TEMP TABLE"
            + " users (id, name, remote_id, provider)"
            + " WITHOUT OIDS"
            + " ON COMMIT DROP"
            + " AS VALUES"
                + " (0, 'bob'::varchar(32), 1, 'Facebook'::varchar(16)),"
                + " (1, 'joe', 1, 'Twitter'),"
                + " (2, 'dave', 2, 'Facebook'),"
                + " (3, 'steve', 5, 'Facebook'),"
                + " (4, 'brian', 4, 'Facebook'),"
                + " (5, 'bill', 3, 'Twitter'),"
                + " (6, 'chris', 3, 'Facebook'),"
                + " (7, 'death', 7, 'Facebook')"
    }, callback);
} // end setupTempTable

function runQuery(_, callback)
{
    // We expect to get rows 0, 4, and 6 back.
    client.query({
        text: "SELECT * FROM users"
                + " WHERE remote_id = ANY($1)"
                + " AND provider = $2",
        values: [
            [1, 3, 4], // remote_id values
            'Facebook', // provider
        ],
    }, callback);
} // end runQuery

function printResults(results, callback)
{
    var rows = results.rows;

    console.log("%d results:", rows.length);
    for(var idx = 0; idx < rows.length; idx++)
    {
        console.log(util.inspect(rows[idx], {colors: true}));
    } // end for

    callback(null, null);
} // end printResults

function shutdown(error)
{
    client = null;
    if(done)
    {
        done();
    } // end if
    done = null;

    if(error)
    {
        console.error("\033[1;31m%s\033[m", error.stack || error.toString());
        console.error("Details:", util.inspect(error, {colors: true}));
    }
    else
    {
        console.log("Exiting.");
    } // end if

    process.exit(error ? 1 : 0);
} // end shutdown