How to assign the query cost to a variable in Postgres

Question

To take the cost esteemed of the query use the EXPLAIN SELECT coluna FROM tabela;, to take the cost current of the query use the EXPLAIN ANALYSE SELECT coluna FROM tabela;, my doubt is how to search the cost of the query automatically, without having to run manually and pick up the eye value.

An example of how it would be:

DECLARE custo integer;
DECLARE maiorcusto integer;
DECLARE query text;

maiorcusto := 0;
i := 0;
query = '';

WHILE i < array_length( queries ,1) LOOP

    custo := explain analyse queries[i];

    IF custo > maiorcusto THEN

        maiorcusto := custo;
        query := queries[i];

    END IF;

    i := i+1;

END LOOP;

The idea is to create a script to find the queries in a log and run on psql, or copy the queries of log to a table in the bank and run with plain sql and check the most expensive, at the moment is just what I look for, then I worry about the real cost of the query ( "cost" X "times executed in the period of time" ), cost of INSERT, UPDATE and DELETE among other things.

I hope this is possible, if it is not, there is another way to seek expensive queries without being checking one by one ?

Edited:

I forgot to tell you, I use Postgres 9.1.

SOLUTION:

Based on @Clodoaldo Neto’s reply :

CREATE or REPLACE function custo_consulta(_consulta text[])
returns table (consulta text, custo_execucao numeric, tempo_execucao numeric ) as '
declare custo text;
begin
    foreach consulta in array _consulta loop
        execute ''EXPLAIN ( FORMAT JSON, ANALYZE ) '' || consulta INTO custo;
        custo_execucao := split_part(split_part(custo, ''"Total Cost": '', 2), '','', 1);
        tempo_execucao := split_part(split_part(custo, ''"Actual Total Time": '', 2), '','', 1);
        return next;
    end loop;
end;
' language plpgsql;

SELECT *
FROM custo_consulta(array['SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''OPORTUNIDADE''', 'SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''REVISAO'''])
ORDER BY custo_execucao desc;

Result
---------------------------------------------------------------------  -----------------  ----------------- 
consulta                                                               custo_execucao     tempo_execucao    
SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'REVISAO'       38426,44           128,267           
SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'OPORTUNIDADE'  38252,65           123,996           

There is only one problem, get error when using $$ and would have to duplicate the ' queries before moving to Function, but this should be corrected.

Answer 1

I did passing an array of queries but can also be referring to a table of queries. Dynamic SQL.

create or replace function custo_consulta(_consulta text[])
returns table (consulta text, planejamento numeric, execucao numeric) as $$
declare custo text;
begin
    foreach consulta in array _consulta loop
        execute 'explain ' || consulta into custo;
        custo := split_part(split_part(custo, '(cost=', 2), ' rows=', 1);
        execucao := split_part(custo, '..', 1);
        planejamento := split_part(custo, '..', 2);
        return next;
    end loop;
end;
$$ language plpgsql;

select *
from custo_consulta(array['select 1','select 1/1'])
order by planejamento + execucao desc;
  consulta  | planejamento | execucao 
------------+--------------+----------
 select 1   |        0.001 |    0.002
 select 1/1 |        0.001 |    0.001