Calling a function inside another function in PL/pgSQL

Question

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.

Are you sure that date returned from function is the same as date you hardcoded? Also, I so not see `nextsunday` function in the second piece of code, where it should be used? — Tomas Greif, Feb 14 '14 at 18:31
@TomasGreif Yes, the function is returning a date type. You can test that and see for yourself. I edited the question and now is showing the nextSunday() function, sorry about that. — XVirtusX, Feb 14 '14 at 18:53
I don't mean type, but value (2014-02-16). What will happen when you try `(date_trunc('week', current_date) + interval '1 week' - interval '1 day')::date` instead of `(select nextsunday())`? Btw. I think you do not need to wrap `nextSunday()` in select - `ba.data = nextsunday()` should also work - for example try `select 1 where nextsunday() > current_date`. It would be good if you add working solution with hard-coded date. — Tomas Greif, Feb 14 '14 at 19:34

Erwin Brandstetter · Accepted Answer · 2021-08-03T13:53:16.250

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:

Ignoring time zones altogether in Rails and PostgreSQL

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.

Ketan Suthar · Answer 2 · 2022-07-04T11:01:56.463

3

In my case, I was calling directly function fun1 from another function like:

CREATE OR REPLACE FUNCTION 
fun2() returns trigger 
LANGUAGE plpgsql VOLATILE AS
$BODY$

BEGIN
raise notice 'trigger fun2 with id: %', new.id;

    fun1(new.id);

    return trigger;

END;

$BODY$;

Resolved issue by calling function be with select statement like:

perform fun1(new.id);

edited Jul 04 '22 at 11:01

answered Jul 04 '22 at 10:38

Ketan Suthar

365
4
13

score 0 · Answer 3 · edited Jun 12 '22 at 13:58

you may call like below

CREATE OR REPLACE FUNCTION "eodb_obps"."fee_calculate_obps_form_engine"("request_guid_code" varchar)
  RETURNS table(fee_calculation_obps text) AS $BODY$

 DECLARE
 json_data  record;
 query varchar; 
BEGIN
    
select eodb_obps_data_json from eodb_obps.eodb_obps_certificate_request_info where eodb_obps_certificate_info_guid = ( select request_ref_identifier
from process.request  where request_guid= request_guid_code )::text into json_data;

query = 'SELECT * from eodb_obps.fee_calculate_obps(  '''||json_data.eodb_obps_data_json::text||''' , ''JSON'')';

raise notice '%',query; 
    
RETURN QUERY EXECUTE query;

end ;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
    
select "eodb_obps"."fee_calculate_obps_form_engine"('d970e702-77c9-4bf7-b43a-cf47ecc64c5e');

Calling a function inside another function in PL/pgSQL

3 Answers3