Geek asks and answers

Calling a function inside another function in PL / PGSQL

I defined a function that always gave me the date of next Sunday. It works fine, here is the code:

CREATE FUNCTION nextSunday() RETURNS date AS $$
DECLARE
    dia_semana INT := CAST(EXTRACT(DOW FROM CURRENT_DATE)as INT);
    dia INT :=  7 - dia_semana;
BEGIN
    RETURN current_date + dia;
END;
$$ LANGUAGE plpgsql

I have another function to dump data to a file, and I need to use the function nextSunday()inside:

CREATE OR REPLACE FUNCTION popularTabelaPessoa() RETURNS VOID AS $$
BEGIN
COPY(SELECT pe.id, pe.fk_naturalidade, pe.fk_documentacao_pessoal, pe.nome, 
       pe.cpf, pe.data_nascimento, pe.sexo, pe.estado_civil, pe.nome_mae,
       pe.data_alteracao, pe.usuario_banco_alteracao,
       pe.usuario_aplicacao_alteracao
FROM fluxo_lt.banca ba
INNER JOIN corporativo.localidade lo
    ON ba.fk_municipio = lo.id
INNER JOIN fluxo_lt.agendamento_candidato ac
    ON ac.fk_banca = ba.id
INNER JOIN info_detran.processo as pr
    ON ac.fk_processo = pr.id
INNER JOIN info_detran.candidato as ca
    ON pr.fk_candidato = ca.id
INNER JOIN corporativo.pessoa as pe
    ON ca.fk_pessoa = pe.id
WHERE ba.data = (SELECT nextSunday())
ORDER BY lo.nome, pe.nome)

TO '/tmp/dump.sql';
END;
$$ LANGUAGE plpgsql

But it does not work. The field ba.datais of datethe same type as the return value of the function nextSunday(). The code runs without errors, but the file is empty. If I tightly bind a date, it works fine. Already tried everything (casting, introducing it into a variable, passed it as an argument to the function), but so far nothing has worked.

I am using Postgres 9.3.

+3
source share
1 answer

, date_trunc():

CREATE FUNCTION next_sunday()
  RETURNS date AS
$func$
SELECT date_trunc('week', now())::date + 6
$func$ LANGUAGE sql

"" - , " ". , :

CREATE FUNCTION next_sunday()
  RETURNS date AS
$func$
SELECT date_trunc('week', CURRENT_DATE + 1)::date + 6
$func$ LANGUAGE sql

→ SQLfiddle

date_trunc('week', now())::date + 6 altogeher.

, :


CREATE OR REPLACE FUNCTION popular_tabela_pessoa()
  RETURNS VOID AS
$func$
BEGIN
COPY(
   SELECT pe.id, pe.fk_naturalidade, pe.fk_documentacao_pessoal, pe.nome
         ,pe.cpf, pe.data_nascimento, pe.sexo, pe.estado_civil, pe.nome_mae
         ,pe.data_alteracao, pe.usuario_banco_alteracao
         ,pe.usuario_aplicacao_alteracao
   FROM   fluxo_lt.banca                 ba
   JOIN   corporativo.localidade         lo ON ba.fk_municipio = lo.id
   JOIN   fluxo_lt.agendamento_candidato ac ON ac.fk_banca = ba.id
   JOIN   info_detran.processo           pr ON ac.fk_processo = pr.id
   JOIN   info_detran.candidato          ca ON pr.fk_candidato = ca.id
   JOIN   corporativo.pessoa             pe ON ca.fk_pessoa = pe.id
   WHERE  ba.data = next_sunday() -- not: (SELECT next_sunday())
 -- or: WHERE  ba.data = date_trunc('week', now())::date + 6
   ORDER  BY lo.nome, pe.nome)
TO '/tmp/dump.sql';

END
$func$ LANGUAGE plpgsql

, . , ? COPY ( )?

+3

More articles:


All Articles