我想为select和changed me数据创建一个函数

CREATE OR REPLACE FUNCTION PublicatedTask( argument ) RETURNS SETOF task AS $$DECLARE
   f task%ROWTYPE;
  BEGIN
    FOR f IN SELECT * FROM Task where layer IN $1 and publicationin<>0 ORDER BY id LOOP
      if (f.publicationIN = 1) then
    f.description='';
    end if;
    RETURN NEXT f;
    END LOOP;
    RETURN;
  END;
  $$
 LANGUAGE 'plpgsql';

但我不知道什么样的论点?
我想做SELECT * FROM PublicatedTask((1,2,3));
谢谢你的帮助

最佳答案

或使用VARIADIC

CREATE OR REPLACE FUNCTION PublicatedTask( VARIADIC argument int[]) RETURNS SETOF task AS $$DECLARE
   f task%ROWTYPE;
  BEGIN
    FOR f IN SELECT * FROM Task where layer = ANY($1) and publicationin<>0 ORDER BY id LOOP
      if (f.publicationIN = 1) then
    f.description='';
    end if;
    RETURN NEXT f;
    END LOOP;
    RETURN;
  END;
  $$
 LANGUAGE 'plpgsql';

用这种方式:
SELECT * FROM PublicatedTask(1,2,3);

VARIADIC从8.4版起提供:http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS

10-06 05:17
查看更多