current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I've a table with VARCHAR as PRIMARY_KEY, so my IDs are randomly generated and looks like WUoN5VemT or MQvOQidTi. I just want write a function which expects an array as input and returns all elements which are contained in the array. I think the following semi-code will show better it a way better:

CREATE OR REPLACE FUNCTION public."getInfo" (
  "myIDs" varchar []
)
RETURNS varchar AS
$body$
BEGIN

    SELECT * FROM "myTable" WHERE "id" IN($1) ORDER BY "idDate" DESC;
    -- RETURN etc....

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

But the problem is the array can contain 50 or more entries (100 are also possible), so I need to do this as fast as possible. The next problem is, the code above doesn't work. I often found the solution for multiple values using the IN-keyword, but is there any way to combine arrays and IN-clause?

Is there any chance to realize this using a function?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Unnest the array and join to the resulting set. This should be faster, especially with long arrays:

CREATE OR REPLACE FUNCTION get_info(_ids varchar[])
 RETURNS SETOF tbl AS
$BODY$
    SELECT *
    FROM   tbl
    JOIN  (SELECT unnest(_ids)) x(id) USING (id)
    ORDER  BY id_date DESC;
$BODY$
LANGUAGE sql STABLE;

This can be a plpgsql function, but an sql function is just as well.

@Clodoaldo already gave some advise on STABLE and the return type. My demo returns the whole row.

Don't just take my word on performance. Run a test with EXPLAIN ANALYZE and, ideally report your findings. :)

share|improve this answer
    
Thanks, but I get the following error by adding "ODER BY id_date DESC": Query failed: ERROR: column "id_date" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...OIN (SELECT unnest($1)) x(id) USING (id) ORDER BY "id_date" ^ QUERY: SELECT (SELECT COUNT(*) FROM "myTable" JOIN (SELECT unnest($1)) x(id) USING (id) ORDER BY "myTable"."id_date" DESC) CONTEXT: PL/pgSQL function "get_info". For testing purposes, I'll count the results of the SELECT-statement. This will be replaced later... –  Nrgyzer Mar 22 '12 at 17:44
    
@Nrgyzer: You changed the function (which I tested and it works) in an impossible way. You cannot count(*) and ORDER BY id_date at the same time. Please update your question with what you actually want to achieve. –  Erwin Brandstetter Mar 22 '12 at 17:52
    
Ah, okay... I just removed COUNT(*) and it works, great... and thanks for help :) –  Nrgyzer Mar 22 '12 at 17:55
up vote 1 down vote 
SELECT * FROM "myTable" WHERE "id" = any($1) ORDER BY "idDate" DESC;

This function is stable not volatile and it returns table or similar not varchar.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.