PostgreSQL - Passing Array to Stored Function
<p>I have two separate databases (MySQL and PostgreSQL) that maintain different data-sets from different departments in my organization-- this can't be changed. I need to connect to one to get a list of <code>symbols</code> or <code>ids</code> from the first DB with a DBAPI in python and request the other set and operate on it.</p>

<p>(I've spent a lot of time on this approach, and it makes sense because of other components in my architecture, so unless there is a <em>much better</em> alternative, I'd like to stick with this method.)</p>

<pre><code>CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION get_results(text[]) RETURNS SETOF record AS $$
SELECT fooname, fooid, foosubid FROM foo WHERE name IN $1;
$$ LANGUAGE SQL;
</code></pre>

<p>In reality my SQL is much more complicated, but I think this method completely describes the purpose. <strong>Can I pass in an arbitrary length parameter into a stored procedure or user defined function and return a result set?</strong></p>

<p>I would like to call the function like: </p>

<pre><code>SELECT * FROM get_results(('Joe', 'Ed'));
SELECT * FROM get_results(('Joe', 'Mary'));
SELECT * FROM get_results(('Ed'));
</code></pre>

<p>I believe using the <code>IN</code> and passing these parameters (if it's possible) would give me the same (or comparable) performance as a <code>JOIN</code>. For my current use case the symbols won't exceed 750-1000 'names', but if performance is an issue here I'd like to know why, as well.</p>