CODEWITHSUNDEEP
postgresqlloopsplpgsql
Marko
Marko

Reputation: 1617

Loop in PL/Postgresql function

I need help, trying to find why it goes to loop again when I run this function. It starts again after returning "OK" and it wont start with truncating the table.

CREATE OR REPLACE FUNCTION schema.table1()
  RETURNS character varying AS
$BODY$
DECLARE var schema.table2;
DECLARE s_doc text;

BEGIN

perform ssv('HTTPS', 'on');
perform ssv('SERVER_NAME', 'www.url.domain');
perform ssv('DAD', 'website');

truncate schema.table3;

for var in (
        select * from schema.table2
) loop
        if var.content like '<?xml version="1.0" encoding="UTF-8"?>\n<XMLDoc format="XMLDOC-XML"%' then
                insert into schema.table3 (id, doc)
                values (var.id, var.content);
        else
                begin
                        s_doc:='<?xml version="1.0" encoding="UTF-8"?>\n'||schema.doc2XMLDOC(var, '1');
                exception when others then
                        s_doc:=NULL;
                end;
                if s_doc is not null then
                        insert into schema.table3 (id, doc)
                        values (var.id, s_doc);
                end if;
        end if;
end loop;

return 'OK';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION schema.export_doc()
  OWNER TO sysadm;

Upvotes: 0

Views: 577

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656251

Obviously, one of the three function calls before truncate does something you are not telling us:

perform ssv('HTTPS', 'on');
perform ssv('SERVER_NAME', 'www.url.domain');
perform ssv('DAD', 'website');

Edit: code audit

A closer look reveals quite a few issues:

  • At the head you name the function schema.table1() (which is a weird name for a function), at the bottom you refer to a schema.export_doc()?

  • You have the keyword DECLARE twice in the function header. You only need that once. To my surprise this does not trigger an error, though.

  • If the \n in

'<?xml version="1.0" encoding="UTF-8"?>\n<XMLDoc format="XMLDOC-XML"%'

is supposed to be interpreted as a line break then you should use proper syntax for escape string constants: 

E'<?xml version="1.0" encoding="UTF-8"?>\n<XMLDoc format="XMLDOC-XML"%'

  • You call a function schema.doc2XMLDOC(var, '1') further down. In mixed case notation? You are aware that identifiers are cast to lower case if not double quoted in PostgreSQL? I never use mixes case identifiers.

  • The exception block looks suspicious. Why would the called function produce an error?

begin
   ...
   exception when others then
      s_doc:=NULL;
end;

There is a number of tests you can run on your data to prevent that:

xml IS DOCUMENT
XMLEXISTS(...)
xml_is_well_formed()

Cleaner and most likely faster than (slow) error handling.

Upvotes: 1

Related Questions