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
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.
I'm using Postgres 9.3.
:= is the assignment operator in PL/pgSQL.
PostgreSQL provides you with a special type called REFCURSOR to declare a cursor variable. First, you specify a variable name for the cursor. Next, you specify whether the cursor can be scrolled backward using the SCROLL . If you use NO SCROLL , the cursor cannot be scrolled backward.
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$;
db<>fiddle here
Old sqlfiddle
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With