Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    2

    Unanswered: passing list of integers into a function (stored procedures)

    I am trying to pass a list of integers (as comma delimeted text = '1,2,3,4') into a function to be used in a WHERE-IN clause. like this:

    Code:
    CREATE OR REPLACE FUNCTION "public"."my_test" (r_list text) RETURNS SETOF integer AS
    $body$
    select id
    from ids_table
    where id in ($1)
    $body$
    LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

    I get an empty results test although it should come out non-empty.
    is this is the way to do such thing?
    I do not want to use dynamic sql from within plpgsql to prevent exposure to sql injections. does anyone have a solution for such case?

    thanks !!!!!!!!
    Last edited by loquin; 09-08-06 at 03:01.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Maybe this would work...

    Code:
    CREATE OR REPLACE FUNCTION "public"."my_test" (r_list text()) RETURNS SETOF integer AS ...
    (passing an array of text to the function.)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Sep 2006
    Posts
    2

    found a solution

    thanks for the suggestion, I wasn't able to compile the function with text[] as an argument. I found a solution that worked for me:
    I created a function to translate the text to setof integers

    CREATE OR REPLACE FUNCTION "public"."text_to_list" (in_list text) RETURNS SETOF integer AS
    $body$
    declare
    v_list alias for $1;
    v_delim text := ',';
    v_arr text[];
    begin
    v_arr := string_to_array(v_list, v_delim);

    for i in array_lower(v_arr, 1)..array_upper(v_arr, 1) loop
    return next v_arr[i]::int;
    end loop;
    return;
    end;
    $body$
    LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

    and than used it from within my original function:

    CREATE OR REPLACE FUNCTION "public"."my_test" (r_list text) RETURNS SETOF integer AS
    $body$
    select id
    from ids_table
    where id in (select * from text_to_list($1))
    $body$
    LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


    thanks for trying to assist.

Posting Permissions

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