Tags: array, arrays, database, examples, function, inplpgsql, mysql, oracle, passing, pgplsql, postgresql, procedure, sql, stored, usedin, values

How to pass array of values to a pgplsql function

On Database » PostgreSQL

5,306 words with 3 Comments; publish: Sat, 24 May 2008 08:31:00 GMT; (25093.99, « »)

Does anyone have any examples of how I would make a stored procedure in

plpgsql that would allow for passing a list or arrays of values to be used

in an sql IN clause? Like so: select * from table where field1 in (values).

Is this possible?

Thanks,

Curtis

--(end of broadcast)--

TIP 4: Have you searched our list archives?

http://archives.postgresql.org

All Comments

Leave a comment...

  • 3 Comments
    • Curtis Scheer wrote:

      > Does anyone have any examples of how I would make a stored procedure in

      > plpgsql that would allow for passing a list or arrays of values to be used

      > in an sql IN clause? Like so: select * from table where field1 in (values

      ).

      > Is this possible?

      >

      Well, a good thing to note here is that there is a very distinct

      semantic difference between an array in postgres and what IN clauses

      take as input: and array

      is a data type whereas IN clauses take a parenthesized list of comma

      separated values. So, if you pass an array into a function wherein

      you then need

      to use those values in an IN clause, you can build yourself an string of

      the values in the array, comma separated of course.

      e.g.

      CREATE OR REPLACE FUNCTION somefunc(ids int[]) RETURNS boolean AS $sf$

      DECLARE

      in_values varchar;

      good int;

      BEGIN

      FOR i IN array_upper(ids, 1) LOOP

      in_values := in_values || ids[i] || ',';

      END LOOP;

      in_values := substring(in_values FROM 1 FOR

      character_length(in_values) - 1); -- this will chop off the last comma

      EXECUTE 'SELECT 1 FROM blah WHERE blah.id IN (' || in_values ||');'

      INTO good;

      IF(good = 1) THEN

      RETURN TRUE;

      ELSE

      RETURN FALSE;

      END IF;

      END;

      $sf$ LANGUAGE plpgsql;

      Or, it may be easier given whatever your situation to simply use the

      array as the argument to a row-wise AND or SOME expression.

      erik jones <erik.postgresql.todaysummary.com.myemma.com>

      software development

      emma(r)

      --(end of broadcast)--

      TIP 1: if posting/reading through Usenet, please send an appropriate

      subscribe-nomail command to majordomo.postgresql.todaysummary.com.postgresql.org so that your

      message can get through to the mailing list cleanly

      #1; Sat, 24 May 2008 08:32:00 GMT
    • Erik Jones wrote:

      > Curtis Scheer wrote:

      > Well, a good thing to note here is that there is a very distinct

      > semantic difference between an array in postgres and what IN clauses

      > take as input: and array

      > is a data type whereas IN clauses take a parenthesized list of comma

      > separated values. So, if you pass an array into a function wherein

      > you then need

      > to use those values in an IN clause, you can build yourself an string

      > of the values in the array, comma separated of course.

      > e.g.

      > CREATE OR REPLACE FUNCTION somefunc(ids int[]) RETURNS boolean AS $sf$

      > DECLARE

      > in_values varchar;

      > good int;

      > BEGIN

      > FOR i IN array_upper(ids, 1) LOOP

      > in_values := in_values || ids[i] || ',';

      > END LOOP;

      > in_values := substring(in_values FROM 1 FOR

      > character_length(in_values) - 1); -- this will chop off the last comma

      > EXECUTE 'SELECT 1 FROM blah WHERE blah.id IN (' || in_values ||');'

      > INTO good;

      > IF(good = 1) THEN

      > RETURN TRUE;

      > ELSE

      > RETURN FALSE;

      > END IF;

      > END;

      > $sf$ LANGUAGE plpgsql;

      > Or, it may be easier given whatever your situation to simply use the

      > array as the argument to a row-wise AND or SOME expression.

      >

      Whoa, replied to this out of the General mailing list before I saw the

      other answers on the SQL list... Sorry guys

      erik jones <erik.postgresql.todaysummary.com.myemma.com>

      software development

      emma(r)

      --(end of broadcast)--

      TIP 9: In versions below 8.0, the planner will ignore your desire to

      choose an index scan if your joining column's datatypes do not

      match

      #2; Sat, 24 May 2008 08:33:00 GMT
    • Thanks, Erik another possible solution would also be this.

      Here it is in case you are interested.

      On 7/18/06, Tony Wasson <ajwasson.postgresql.todaysummary.com.gmail.com> wrote:[vbcol=seagreen]

      > On 7/18/06, Curtis Scheer <Curtis.postgresql.todaysummary.com.daycos.com> wrote:

      field1 in (values).[vbcol=seagreen]

      Ahhh... Here's an example using Tom's recommended field=ANY (arrayvalue)

      SQL.

      CREATE TABLE ids

      (

      id INTEGER

      , PRIMARY KEY (id)

      );

      INSERT INTO ids VALUES (1);

      INSERT INTO ids VALUES (2);

      INSERT INTO ids VALUES (3);

      CREATE OR REPLACE FUNCTION example_array_input(INT[]) RETURNS SETOF ids

      AS

      $BODY$ DECLARE

      in_clause ALIAS FOR $1;

      clause TEXT;

      rec RECORD;

      BEGIN

      FOR rec IN SELECT id FROM ids WHERE id = ANY(in_clause)

      LOOP

      RETURN NEXT rec;

      END LOOP;

      -- final return

      RETURN;

      END

      $BODY$ language plpgsql;

      SELECT * FROM example_array_input('{1,2,4,5,6}'::INT[]);

      --(end of broadcast)--

      [vbcol=seagreen]

      --(end of broadcast)--

      TIP 4: Have you searched our list archives?

      http://archives.postgresql.org

      #3; Sat, 24 May 2008 08:34:00 GMT