How to return a table and its total count in postgres' function

Question

I am newer to postgresql and create function as the below

CREATE OR REPLACE FUNCTION GetLogs(
    _from Date, 
    _to Date,  
    _sortBy TEXT, 
    _orderby INT, 
    _page INT, 
    _row INT)
 RETURNS TABLE (
     "Id" UUID, 
     "RequestHeaders" text, 
     "RequestContent" text, 
     "ResponseHeaders" text, 
     "ResponseContent" text, 
     "ResponseCode" integer, 
     "ExecutionTimeMs" integer, 
     "Description" text,
     "RecordedOn" timestamp with time zone, 
     "Username" character varying, 
     "Exception" text) 
AS $$
BEGIN
    RETURN QUERY 
        SELECT COUNT(1)
        FROM dbo."Logs" log
        where log."RecordedOn" >= $1 and log."RecordedOn" <= $2;

    RETURN QUERY 
        SELECT log.*
        FROM dbo."Logs" log
        where log."RecordedOn" >= $1 and log."RecordedOn" <= $2
        Offset ($5-1) * $6
        Limit $6;
END;
$$ LANGUAGE plpgsql;

, and call it using

select * from GetLogs('2020-02-11','2020-02-12','Date',0,1,10)

I expect it will be a count and table data, but error is

ERROR:  structure of query does not match function result type
DETAIL:  Returned type bigint does not match expected type uuid in column 1.

can anybody solve it ?

The first RETURN QUERY still needs to return the structure you defined that function to return. Instead you return a single BIGINT. What format exactly are you expecting to get out of this function? Show an example with the output that you expect to get from a call to the function. — 404
– 404, Commented Feb 12, 2020 at 10:57

Slava Rozhnev · Accepted Answer · 2020-02-13 08:14:15Z

2

You can not return result with structure not matched to RETURNS TYPE (in your case - TABLE). Instead this you can use window function for add RowsCount to each returned row.

CREATE OR REPLACE FUNCTION GetLogs(
    _from Date, 
    _to Date,  
    _sortBy TEXT, 
    _orderby INT, 
    _page INT, 
    _row INT)
 RETURNS TABLE (
     "Id" UUID, 
     "RequestHeaders" text, 
     "RequestContent" text, 
     "ResponseHeaders" text, 
     "ResponseContent" text, 
     "ResponseCode" integer, 
     "ExecutionTimeMs" integer, 
     "Description" text,
     "RecordedOn" timestamp with time zone, 
     "Username" character varying, 
     "Exception" text,
     "RowsCount" integer
)
AS $$
DECALARE
    RowsCount integer;
BEGIN

    RETURN QUERY 
        SELECT log.*, COUNT(*) OVER() AS RowsCount
        FROM dbo."Logs" log
        WHERE log."RecordedOn" >= $1 AND log."RecordedOn" <= $2
        OFFSET ($5-1) * $6
        LIMIT $6;
END;
$$ LANGUAGE plpgsql;

edited Feb 13, 2020 at 8:14

answered Feb 12, 2020 at 13:42

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

2 Comments

letitbe Over a year ago

Can it return multiple result ?

Slava Rozhnev Over a year ago

I'm not sure if it possible to return 2 results with different types

Collectives™ on Stack Overflow

How to return a table and its total count in postgres' function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related