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

by

Yes you can. There are 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 valid, 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 vlaid, 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:

db<>fiddle here – demonstrating all (incl. call)

More Related Contents:

  1. Define table and column names as arguments in a plpgsql function?
  2. Loop on tables with PL/pgSQL in Postgres 9.0+
  3. Table name as a PostgreSQL function parameter
  4. What are ‘$$’ used for in PL/pgSQL
  5. INSERT with dynamic table name in trigger function
  6. The forgotten assignment operator “=” and the commonplace “:=”
  7. Truncating all tables in a Postgres database
  8. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  9. How to set value of composite variable field using dynamic SQL
  10. How do I do large non-blocking updates in PostgreSQL?
  11. Execute a dynamic crosstab query
  12. Is there a way to disable function overloading in Postgres
  13. PostgreSQL function returning multiple result sets
  14. Dynamic SQL (EXECUTE) as condition for IF statement
  15. User defined variables in PostgreSQL
  16. Returning from a function with OUT parameter
  17. SELECT raises exception in PL/pgSQL function
  18. Test for null in function with varying parameters
  19. Postgres FOR LOOP
  20. Dynamically generate columns for crosstab in PostgreSQL
  21. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  22. No function matches the given name and argument types
  23. Cursor based records in PostgreSQL
  24. ERROR: unterminated quoted string at or near
  25. How can I send some http request from postgresql function or trigger
  26. Select from a table variable
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment