PostgreSQL: Call a Stored Procedure for Every Row
1 min read

PostgreSQL: Call a Stored Procedure for Every Row

PostgreSQL: Call a Stored Procedure for Every Row

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

SELECT your_procedure_name(parameters);

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";

A Short Explanation

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.

Simple.

HTH,