Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
912 views
in Technique[技术] by (71.8m points)

postgresql - Optional argument in PL/pgSQL function

I am trying to write a PL/pgSQL function with optional arguments. It performs a query based on a filtered set of records (if specified), otherwise performs a query on the entire data set in a table.

For example (PSEUDO CODE):

CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids=[]) RETURNS SETOF RECORD AS $$
    IF len(optional_list_of_ids) > 0 THEN
        RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2 AND id in optional_list_of_ids);
    ELSE
        RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2);
    ENDIF
$$ LANGUAGE SQL;

What would be the correct way to implement this function?

As an aside, I would like to know how I could call such a function in another outer function. This is how I would do it - is it correct, or is there a better way?

CREATE FUNCTION foofuncwrapper(param1 integer, param2 date, param2 date) RETURNS SETOF RECORD AS $$
BEGIN
   CREATE TABLE ids AS SELECT id from foobar where id < 100;
   RETURN QUERY (SELECT * FROM foofunc(param1, param2, ids));
END
$$ LANGUAGE SQL
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Since PostgreSQL 8.4 (which you seem to be running), there are default values for function parameters. If you put your parameter last and provide a default, you can simply omit it from the call:

CREATE OR REPLACE FUNCTION foofunc(_param1 integer
                                 , _param2 date
                                 , _ids    int[] DEFAULT '{}')
  RETURNS SETOF foobar         -- declare return type!
  LANGUAGE plpgsql AS
$func$
BEGIN  -- required for plpgsql
   IF _ids <> '{}'::int[] THEN  -- exclude empty array and NULL
      RETURN QUERY
      SELECT *
      FROM   foobar
      WHERE  f1 = _param1
      AND    f2 = _param2
      AND    id = ANY(_ids);    -- "IN" is not proper syntax for arrays
   ELSE
      RETURN QUERY
      SELECT *
      FROM   foobar
      WHERE  f1 = _param1
      AND    f2 = _param2;
   END IF;
END  -- required for plpgsql
$func$;

Major points:

  • The keyword DEFAULT is used to declare parameter defaults. Short alternative: =.

  • I removed the redundant param1 from the messy example.

  • Since you return SELECT * FROM foobar, declare the return type as RETURNS SETOF foobar instead of RETURNS SETOF record. The latter form with anonymous records is very unwieldy, you'd have to provide a column definition list with every call.

  • I use an array of integer (int[]) as function parameter. Adapted the IF expression and the WHERE clause accordingly.

  • IF statements are not available in plain SQL. Has to be LANGUAGE plpgsql for that.

Call with or without _ids:

SELECT * FROM foofunc(1, '2012-1-1'::date);

Effectively the same:

SELECT * FROM foofunc(1, '2012-1-1'::date, '{}'::int[]);

You have to make sure the call is unambiguous. If you have another function of the same name and two parameters, Postgres might not know which to pick. Explicit casting (like I demonstrate) narrows it down. Else, untyped string literals work, too, but being explicit never hurts.

Call from within another function:

CREATE FUNCTION foofuncwrapper(_param1 integer, _param2 date)
  RETURNS SETOF foobar
  LANGUAGE plgpsql AS
$func$
DECLARE
   _ids int[] := '{1,2,3}';
BEGIN
   -- whatever

   RETURN QUERY
   SELECT * FROM foofunc(_param1, _param2, _ids);
END
$func$;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...