在PL / PGSQL中的另一个函数中调用函数 [英] Calling a function inside another function in PL/PGSQL

问题描述

我定义了一个函数，总是给我下个星期天的日期。它的工作正常，这里是代码：

I defined a function to always give me the date of the 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

我有另一个功能将数据转储到文件中，我需要使用 nextSunday（）函数里面：

I have another function to dump data into a file and I need to use nextSunday() function 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

但它不工作。 ba.data 是 date ，与 nextSunday（）的返回值相同的类型， 函数。代码执行没有任何错误，但该文件为空。如果我硬编码一个日期，它的工作正常。已经尝试了所有的东西（铸造，放入一个变量，作为一个参数传递给函数），但没有任何工作到目前为止。

But it is not working. The field ba.data is date, the same type as return value of nextSunday() function. The code is executed without any errors, but the file is blank. If I hardcode a date it works just fine. Already tried everything (casting, putting it into a variable, pass as a argument to the function) but nothing worked so far.

我正在使用Postgres 9.3。 p>

I'm using Postgres 9.3.

推荐答案

首先，你的函数可以简单得多， date_trunc（） ：

First of all, your function can be much simpler with date_trunc():

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

如果今天是星期天，则返回为下周日。或者，在这种情况下跳过一周：

If "today" is a Sunday, it is returned as "next Sunday". Alternatively, to skip ahead one week in this case:

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

接下来，简化通话：

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失败。你确定，你的查询返回任何行？你试过一个手册 COPY （没有函数包装器）？

However, this cannot explain why your COPY fails. Have you made sure, your query returns any rows? And have you tried a manual COPY (without the function wrapper)?

这篇关于在PL / PGSQL中的另一个函数中调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！