postgresql 在 PL/PGSQL 中调用另一个函数内的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21786535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling a function inside another function in PL/PGSQL
提问by XVirtusX
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
I have another function to dump data into a file and I need to use nextSunday()function inside:
我有另一个函数可以将数据转储到文件中，我需要在nextSunday()里面使用函数：
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 is not working. The field ba.datais 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.
但它不起作用。字段ba.data为date，与nextSunday()函数的返回值类型相同。代码执行没有任何错误，但文件是空白的。如果我对日期进行硬编码，它就可以正常工作。已经尝试了一切（转换，将其放入变量，作为参数传递给函数）但到目前为止没有任何效果。
I'm using Postgres 9.3.
我正在使用 Postgres 9.3。
采纳答案by Erwin Brandstetter
First of all, your function can be much simpler with date_trunc():
首先，您的功能可以通过以下方式简单得多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
You might just use date_trunc('week', now())::date + 6instead and drop the function altogeher.
您可以date_trunc('week', now())::date + 6改为使用并完全删除该功能。
Next, simplify the call:
接下来，简化调用：
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 plpgsqlHowever, this cannotexplain why your COPY fails. Have you made sure, your query returns any rows? And have you tried a manual COPY(without the function wrapper)?
但是，这不能解释为什么您的 COPY 失败。您确定您的查询返回任何行吗？您是否尝试过手册COPY（没有函数包装器）？
