Search code examples
postgresqlfunctionvariablesparametersplpgsql

Can I make a plpgsql function return an integer without using a variable?

Something like this: 

CREATE OR REPLACE FUNCTION get(param_id integer)
  RETURNS integer AS
$BODY$
BEGIN
SELECT col1 FROM TABLE WHERE id = param_id;
END;
$BODY$
  LANGUAGE plpgsql;

I would like to avoid a DECLARE just for this.

Solution

  • Yes you can. There is a number of ways.

    1) RETURN (SELECT ...)

    CREATE OR REPLACE FUNCTION get_1(_param_id integer)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN _param_id;
-- Or:
-- RETURN (SELECT col1 FROM tbl WHERE id = _param_id);
END
$func$;

    2) Use an OUT or INOUT parameter

    CREATE OR REPLACE FUNCTION get_2(_param_id integer, OUT _col1 integer)
-- RETURNS integer -- is optional noise in this case
  LANGUAGE plpgsql AS  
$func$
BEGIN
   SELECT INTO _col1  col1 FROM tbl WHERE id = _param_id;

   -- also possible (currently), but discouraged:
   -- _col1 := col1 FROM tbl WHERE id = _param_id;
END
$func$;

    More in the manual here.

    3) (Ab)use IN parameter

    Since Postgres 9.0 you can also use input parameters as variables. The release notes for 9.0:

    An input parameter now acts like a local variable initialized to the passed-in value.

    CREATE OR REPLACE FUNCTION get_3(_param_id integer)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT INTO _param_id  col1 FROM tbl WHERE id = _param_id;
   RETURN _param_id;

   -- also possible (currently), but discouraged:
   -- $1 := col1 FROM tbl WHERE id = $1;
   -- RETURN $1;
END
$func$;

    Variants 2) and 3) do use a variable implicitly, but you don't have to DECLARE one explicitly (as requested).

    4) Use a DEFAULT value with an INOUT parameter

    This is a bit of a special case. The function body can be empty.

    CREATE OR REPLACE FUNCTION get_4(_param_id integer, INOUT _col1 integer = 123)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- You can assign some (other) value to _col1:
   -- SELECT INTO _col1  col1 FROM tbl WHERE id = _param_id;
   -- If you don't, the DEFAULT 123 will be returned.
END
$func$;

    INOUT _col1 integer = 123 is short notation for INOUT _col1 integer DEFAULT 123. See:

    5) Use a plain SQL function instead

    CREATE OR REPLACE FUNCTION get_5(_param_id integer)
  RETURNS integer
  LANGUAGE sql AS
'SELECT col1 FROM tbl WHERE id = _param_id';

    Or use use param reference $1 instead of param name.

    Variant 5) one uses plain single quotes for the function body. All the same. See:

    fiddle - demonstrating all (incl. call)