CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Stim
Stim

Reputation: 63

PL/pgSQL , How to make a function using Raise notices and export the messages from the console to a text file from the Code

I have to make a update function that have multiple conditions like this 

BEGIN
  OPEN cur3 FOR execute('select id_organigramme from ( select distinct  id_personne,id_organigramme,idfax  from requpdate where
    id_personne= ' || VariableIDpersonne || ' and idfax is  null) a where
    a.id_organigramme not in (select distinct   id_organigramme  from
    requpdate where id_personne= ' ||VariableIDpersonne || ' and idfax is
    not null and a.id_personne=requpdate.id_personne )  ');
  LOOP
    FETCH cur3 INTO VariableIDorganigrammeFax;

    if not found then
      --Message here !!!
      --Raise notice 'hello word!'   
      exit;
    end if;

I have to show up messages if any condition exists I found out that I can do this with Raise Notice/info ... statement, but I have to make auto export of those messages into a text file when the function finishes. Is this possible? Otherwise what can I use to make it.

I use PGAdminIII as a client.

Upvotes: 0

Views: 683

Answers (1)

Patrick
Patrick

Reputation: 32244

What your logging options are depends entirely on your client configuration. But rather than using RAISE NOTICE I would suggest you use the NOTIFY \ LISTEN framework. Basically, in your function you issue a notice to a channel of your choosing (can be any string) and in your client you listen to that same channel, logging the messages as they come in. How exactly the listening and logging works depends on your client.

The code you show can also you use some improvements.

First of all, your query is an incredibly convoluted version of:

SELECT DISTINCT id_organigramme
FROM requpdate
WHERE id_personne = VariableIDpersonne
  AND idfax IS NULL;

Secondly, you do not need a dynamic query, you can get by with variable substitution. Assuming id_personne is not a string, it is as simple as stated above, otherwise use quote_literal(VariableIDpersonne).

Lastly, unless there are parts of your function not shown that require a cursor, you can simply do:

FOR VariableIDorganigrammeFax IN [query above]
LOOP
  ... -- do your processing here
END LOOP;

IF NOT FOUND THEN  -- the loop above did not iterate because no records were returned
  SELECT pg_notify('logMyFunction', format('%s: No records found', VariableIDpersonne));
END IF;

The pg_notify() function is a wrapper around the NOTIFY command that makes it possible to pass variable strings.

Before you call the function, you should issue the command LISTEN logMyFunction so that your session will receive the notifications from the channel.

Upvotes: 1

Related Questions