I guess that newbies in PostgreSQL like me have first question when they create a store procedure:
How do I call it?
The answer is simple; you just execute
Then, usually immediately the second question pops up:
I wrote a procedure which I need to call for each row in a table. How do I do that?
The answer is, just as simple; you execute
SELECT your_procedure_name(parameters) FROM "your_table";
Let’s assume a table:
CREATE TABLE "REFERENCE_XML_DATA" ( CONTENT xml, ASIDE xml, ID character varying(20) NOT NULL, CONSTRAINT XML_PK PRIMARY KEY (ID) );
and a procedure:
CREATE OR REPLACE FUNCTION populate_relational(ID varchar, PRIMARY_CONTENT xml, SECONDARY_CONTENT xml) RETURNS void AS $$ DECLARE BEGIN RAISE NOTICE 'A simple text'; END $$ LANGUAGE plpgsql;
The procedure is designed to be called for each row in the REFERENCE_XML_DATA table and perform some processing.
The calling statement is:
select populate_relational(ID, CONTENT, ASIDE) from "REFERENCE_XML_DATA";
Then, the CONTENT and ASIDE fields in the REFERENCE_XML_DATA will be interpreted as PRIMARY_CONTENT and SECONDARY_CONTENT respectively.