PL/pgSQL - return single record from FUNCTION

Question

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:

field1 , 0
field1 , field2

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

user330315 · Accepted Answer · 2017-01-09 13:39:18Z

13

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.

answered Jan 9, 2017 at 13:39

user330315

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

1 Comment

Louis Lee Over a year ago

this would be the answer i am looking for

klin · Accepted Answer · 2021-09-24 16:47:32Z

6

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).

edited Sep 24, 2021 at 16:47

answered Jan 9, 2017 at 13:25

klin

123k15 gold badges240 silver badges262 bronze badges

2 Comments

Rinat Over a year ago

Code above is not solve a problem "return single record from FUNCTION". Try to exedute this code:

sql CREATE OR REPLACE FUNCTION get_performance_achieve ()     RETURNS TABLE (       field1 INT,       field2 INT   ) AS $datarows$ BEGIN     field1 := (select 1);     field2 := (select 2);     RETURN NEXT; END; $datarows$ LANGUAGE 'plpgsql';  select (get_performance_achieve()).field1 is not null and true;

Result: [42804] ERROR: argument of AND must not return a set

klin Over a year ago

@Rinat - I have added some explanations.

Collectives™ on Stack Overflow

PL/pgSQL - return single record from FUNCTION

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related