Calling a function inside another function in PL/pgSQL
First of all, your function can be much simpler with date_trunc()
:
CREATE FUNCTION next_sunday()
RETURNS date
LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT date_trunc('week', LOCALTIMESTAMP)::date + 6;
$func$
PARALLEL SAFE
only for Postgres 9.6 or later.
If you have to consider time zones, see:
If "today" is a Sunday, the above returns it as "next Sunday".
To skip ahead one week in this case:
CREATE FUNCTION next_sunday()
RETURNS date
LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT date_trunc('week', LOCALTIMESTAMP + interval '1 day')::date + 6;
$func$;
Or just use date_trunc('week', LOCALTIMESTAMP)::date + 6
directly, instead of the function.
Next, simplify the call:
CREATE OR REPLACE FUNCTION popular_tabela_pessoa()
RETURNS VOID
LANGUAGE plpgsql 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())
-- WHERE ba.data = date_trunc('week', LOCALTIMESTAMP)::date + 6 -- direct alternative
ORDER BY lo.nome, pe.nome)
TO '/tmp/dump.sql';
END
$func$;
However, this cannot explain why your COPY
fails. Have you made sure the query returns any rows? And have you tried a manual COPY
without the function wrapper?
You need the necessary privileges for COPY TO
\copy
in psql may be an alternative.
XVirtusX
Updated on August 04, 2021Comments
-
XVirtusX almost 3 years
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: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.data
isdate
, the same type as return value ofnextSunday()
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.I'm using Postgres 9.3.