Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Unanswered: select using arrays

    select using an array




    I have a Postgres function where one of its' parameters is an array. I want to use the values in the array in a WHERE clause. My codeis below. It is the line: where I'm saying ANY (costfiles) that I'm having trouble with.

    CREATE OR REPLACE FUNCTION getmanagersaep(costfiles bigint[], manager bigint, level integer)
    RETURNS SETOF managers AS
    $BODY$
    DECLARE
    RECORD_OWN_USER managers;
    RECORD_CHILD managers;
    WEMUSERS_REC wem_users;
    costFiles ALIAS for $1;
    level int;
    BEGIN
    FOR RECORD_OWN_USER in
    select distinct m.manager_id id,u.name reporter_name from wem_users m, wem_users u , costs where m.manager_id=u.id
    and m.customer_id=(select customer from wem_cost_file where id =costfiles[1] )
    and costs.wem_user=u.id
    and costs.cost_file in costfiles = ANY (costfiles)
    LOOP
    RETURN NEXT RECORD_OWN_USER;
    END LOOP;

    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100
    ROWS 1000;
    ALTER FUNCTION getmanagersaep(bigint[], bigint, integer)
    OWNER TO voxware;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    In the expression
    Code:
    and costs.cost_file in costfiles = ANY (costfiles)
    the part IN COSTFILES doesn't make sense.

    You probably meant to write
    Code:
    and costs.cost_file = ANY(costfiles)
    (Note the missing in costfiles part).

    And please learn how to use [code] tags to format your code properly.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •