PostgreSQL - Passing Array to Stored Function

Question

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.

fl0cke · Accepted Answer · 2016-02-04 22:12:37Z

3

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

edited Feb 4, 2016 at 22:12

answered Feb 4, 2016 at 21:55

fl0cke

2,9041 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

klin · Accepted Answer · 2016-02-04 22:55:56Z

1

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)

edited Feb 4, 2016 at 22:55

answered Feb 4, 2016 at 22:01

klin

123k15 gold badges240 silver badges262 bronze badges

Comments

aelkz · Accepted Answer · 2016-02-04 21:56:12Z

1

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;

answered Feb 4, 2016 at 21:56

aelkz

1,8952 gold badges27 silver badges28 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Passing Array to Stored Function

3 Answers 3

Row vs Array constructor

Variadic functions

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Row vs Array constructor

Variadic functions

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related