Query_time SQL as a function w/o creating a new type
От | Ow Mun Heng |
---|---|
Тема | Query_time SQL as a function w/o creating a new type |
Дата | |
Msg-id | 1193379886.10340.6.camel@neuromancer.home.net обсуждение исходный текст |
Ответы |
Re: Query_time SQL as a function w/o creating a new type
|
Список | pgsql-general |
Hi, After Erik Jones gave me the idea for this, I started to become lazy to have to type this into the sql everytime I want to see how long a query is taking.. so, I thought that I'll create a function to do just that.. I ended up with.. CREATE OR REPLACE FUNCTION query_time() RETURNS SETOF query_time AS $BODY$ DECLARE rec RECORD; BEGIN FOR rec IN SELECT procpid, client_addr, now() - query_start as query_time, current_query FROM pg_stat_activity ORDER BY query_time DESC LOOP RETURN NEXT rec; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; But the issue with the above is that I need to create a type. CREATE TYPE query_time AS (procpid integer, client_addr inet, query_time interval, current_query text); Is there a method which I'm able to return a result set w/o needing to declare/create a new type. I tried to use language 'sql' but it only returned me 1 column, with all the fields concatenated together with comma separating the fields.
В списке pgsql-general по дате отправления: