CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
Jared
Jared

Reputation: 3892

PostgreSQL - Passing Array to Stored Function

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 symbols or ids from the first DB with a DBAPI in python and request the other set and operate on it.

(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 much better alternative, I'd like to stick with this method.)

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;

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

I would like to call the function like: 

SELECT * FROM get_results(('Joe', 'Ed'));
SELECT * FROM get_results(('Joe', 'Mary'));
SELECT * FROM get_results(('Ed'));

I believe using the IN and passing these parameters (if it's possible) would give me the same (or comparable) performance as a JOIN. 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.

Upvotes: 1

Views: 1833

Answers (3)

klin
klin

Reputation: 121494

Use RETURNS TABLE instead of RETURNS SETOF record. This will simplify the function calls.

You cannot use IN operator in that way. Use ANY instead.

CREATE FUNCTION get_results(text[]) 
RETURNS TABLE (fooname text, fooid int, foosubid int) 
AS $$
    SELECT fooname, fooid, foosubid 
    FROM foo 
    WHERE fooname = ANY($1);
$$ LANGUAGE SQL;

SELECT * FROM get_results(ARRAY['Joe']);

 fooname | fooid | foosubid 
---------+-------+----------
 Joe     |     1 |        1
(1 row)

If the function returns setof records you have to put a column definition list in every function call:

SELECT * 
FROM get_results(ARRAY['Joe']) AS (fooname text, fooid int, foosubid int)

Upvotes: 1

fl0cke
fl0cke

Reputation: 2884

Row vs Array constructor

('Joe', 'Ed') is equivalent to ROW('Joe', 'Ed') and creates a new row.

But your function accepts an array. To create one, call it with an Array constructor:

SELECT * FROM get_results(ARRAY['Joe', 'Ed']);

Variadic functions

You can declare your input parameter as VARIADIC like so

CREATE FUNCTION get_results(VARIADIC text[]) RETURNS SETOF record AS $$
SELECT fooname, fooid, foosubid FROM foo WHERE name = ANY($1);
$$ LANGUAGE SQL;

It accepts a variable number of arguments. You can call it like this:

SELECT * FROM get_results('Joe', 'Ed');

More on functions with variable length arguments: http://www.postgresql.org/docs/9.4/static/xfunc-sql.html

Upvotes: 2

aelkz
aelkz

Reputation: 1875

Jared, If you want just to pass an array into a procedure or function call in where clause, you can try using this:

CREATE FUNCTION get_results(text[]) RETURNS SETOF record AS $$
SELECT fooname, fooid, foosubid FROM foo WHERE name = any($1);
$$ LANGUAGE SQL;

Upvotes: 1

Related Questions