Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 672
johnlemon Avatar asked Nov 17 '11 15:11

johnlemon


People also ask

How do you call a function in Plpgsql?

The normal syntax to call another PL/pgSQL function from within PL/pgSQL is to either reference the function in a SQL SELECT statement, or during the assignment of a variable. For example: SELECT function_identifier ( arguments ); variable_identifier := function_identifier ( arguments );

How do I return a function in postgresql?

To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ). In the function, we return a query that is a result of a SELECT statement.

What is Setof in postgresql?

Alternatively, an SQL function can be declared to return a set (that is, multiple rows) by specifying the function's return type as SETOF sometype , or equivalently by declaring it as RETURNS TABLE( columns ) . In this case all rows of the last query's result are returned. Further details appear below.


1 Answers

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:

  • The forgotten assignment operator "=" and the commonplace ":="

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:

  • What are '$$' used for in PL/pgSQL
  • Insert text with single quotes in PostgreSQL

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

like image 161
Erwin Brandstetter Avatar answered Oct 04 '22 20:10

Erwin Brandstetter