48

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.

0

1 Answer 1

103

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)

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.