CODEWITHSUNDEEP
postgresqlplpgsql
Louis Lee
Louis Lee

Reputation: 281

PL/pgSQL - return single record from FUNCTION

I am creating a SP using PL/pgSQL:

CREATE OR REPLACE FUNCTION get_performance_achieve (p_month INT,p_year INT, p_uid INT) 
RETURNS TABLE (
field1 INT,
field2 INT
) 
AS $datarows$
DECLARE var_r record;
BEGIN
field1 :=0;
field2 :=0;

FOR var_r IN(select COUNT(id) as counter from "TABLE_A" 
       )  
    LOOP
    field1 := (var_r.counter) ; 
    RETURN NEXT;
END LOOP;

FOR var_r IN(select COUNT(id) as counter from "TABLE_B" 
       )  
    LOOP
    field2 := (var_r.counter) ; 
    RETURN NEXT;
END LOOP;

END; 
$datarows$
LANGUAGE 'plpgsql';

when I execute the SP, it will return 2 records:

  1. field1 , 0
  2. field1 , field2

How can I only return a single record with field1, field2

Upvotes: 7

Views: 7056

Answers (2)

klin
klin

Reputation: 121854

Loops are redundant in this case, use simple assignments:

CREATE OR REPLACE FUNCTION get_performance_achieve (p_month INT,p_year INT, p_uid INT) 
RETURNS TABLE (
    field1 INT,
    field2 INT
) 
AS $datarows$
BEGIN
    field1 := (select COUNT(id) as counter from "TABLE_A");
    field2 := (select COUNT(id) as counter from "TABLE_B");

    RETURN NEXT;
END; 
$datarows$
LANGUAGE plpgsql;

Note, that the function remains a set-returning function though it returns a single row. Hence it should be used in the FROM clause:

SELECT * FROM get_performance_achieve(1,2017,42);

The variant with OUT parameters described in the other answer returns strictly one row and seems simpler. Such a function may be called from a SELECT list. However, I would not recommend using it in the way:

SELECT (get_performance_achieve(1,2017,42)).*;

because the function is called as many times as the number of columns in the result (twice in this case).

Upvotes: 6

user330315
user330315

Reputation:

I don't see the reason why you are using returns table if you want to return a single record. A simple returns record will do exactly that: return a single record:

CREATE OR REPLACE FUNCTION get_performance_achieve (p_month INT,p_year INT, p_uid INT) 
   RETURNS record --<< here
AS $datarows$
DECLARE
  l_count1 bigint;
  l_count2 bigint;
BEGIN
  select COUNT(id) into l_count1 from "TABLE_A";
  select COUNT(id) into l_count2 from "TABLE_B";
  RETURN (l_count1, l_count2);
END; 
$datarows$
LANGUAGE plpgsql;

To run it, use e.g.:

select get_performance_achieve(1,2017,42);

The above however creates an anonymous record where the fields do not have a name.

If you want that, declare two OUT parameters instead:

CREATE OR REPLACE FUNCTION get_performance_achieve (p_month INT,p_year INT, p_uid INT, field1 out integer, field2 out integer) 
AS $datarows$
BEGIN
  select COUNT(id) into field1 from "TABLE_A";
  select COUNT(id) into field2 from "TABLE_B";
  RETURN (l_count1, l_count2);
END; 
$datarows$
LANGUAGE plpgsql;

You call it the same way:

select get_performance_achieve(1,2017,42);

And it will return e.g. (1,2)

If you want to see each field of the record as a column of the result, you need to use:

select (get_performance_achieve(1,2017,42)).*;

The above would not be possible with an anonymous record as shown in the first example.

Upvotes: 12

Related Questions