Returning a table with fields, not records in PostgreSQL

Question

I have this function

drop function uptimes(inicio date, fim date);
create or replace function uptimes(inicio date, fim date) 
    returns table (id integer, entrada timestamp with time zone,
    saida timestamp with time zone, erro boolean, uptime interval) as
$$
begin
    create temporary table localdata (id_local integer,
      entrada_local timestamp with time zone,
      saida_local timestamp with time zone, erro_local boolean) on commit drop ;
    insert into localdata select * from uptime u
    WHERE u.saida>=inicio and u.entrada<=fim order by u.entrada ;

    alter table localdata add uptime_local interval;
    update localdata set saida_local=fim where saida_local>fim;
    update localdata set entrada=inicio where entrada<inicio;
    update localdata set uptime_local=saida_local-entrada_local;
    return query select * from localdata;
end;
$$
language plpgsql;

That I use to calculate some uptime from a internal system.

However, I receive as output:

uptimes
-----------------------------------------------------------------------------
(1537,"2015-10-12 07:04:15-03","2015-10-14 05:01:56-03",f,"1 day 21:57:41")

And what I need is to split the record into the fields as the output of this, like:

id  | entrada                | saida                  |erro |uptime
----+------------------------+------------------------+-----+------------------
1537|"2015-10-12 07:04:15-03"|"2015-10-14 05:01:56-03"|f    |"1 day 21:57:41"

How do you call this function? You need to use select * from uptimes(...); — user330315
– user330315, Commented Nov 19, 2015 at 13:51
I call as select uptimes ('2015-10-14','2015-11-15), for exemple — HufflepuffBR
– HufflepuffBR, Commented Nov 19, 2015 at 13:51
Well, that's wrong. The function returns a table so you need to put it into the from clause. — user330315
– user330315, Commented Nov 19, 2015 at 13:52
Thanks @a_horse_with_no_name. I'm starting into stored procedure world. :( — HufflepuffBR
– HufflepuffBR, Commented Nov 19, 2015 at 13:53

user330315 · Accepted Answer · 2015-11-19 14:46:18Z

3

For a function that is declared as returns table, you need to put the function call into the from clause:

select *
from uptimes('2015-10-14','2015-11-15);

answered Nov 19, 2015 at 14:46

user330315

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

Collectives™ on Stack Overflow

Returning a table with fields, not records in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related