Loop in PL/Postgresql function

Question

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;

Erwin Brandstetter · Accepted Answer · 2011-11-26 16:07:36Z

1

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.

edited Nov 26, 2011 at 16:07

answered Nov 26, 2011 at 5:20

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marko Over a year ago

Can it somehow affect the script so it would start looping without truncating?

Erwin Brandstetter Over a year ago

@Marko: For instance if you call schema.table1() in ssv.

Collectives™ on Stack Overflow

Loop in PL/Postgresql function

1 Answer 1

Edit: code audit

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit: code audit

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related