Create procedure to execute query in PostgreSQL

Question

Maybe it's not new case, but I'm stack about it. This is the procedure that I use to run query, it run normally in MySQL, but not in PostgreSQL and I don't know how to do that. The procedure(in MySQL) looks like :

CREATE PROCEDURE runstatement(IN statement TEXT)
BEGIN 
  SET @s = statement; 
  IF LENGTH(@s) <> 0 THEN PREPARE stmt FROM @s; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 
  END IF; 
END

The questions:

1. How do I convert it to PostgreSQL version?
2. How do I call this procedure(runstatement) when I need it in another procedure? In MySQL I know as CALL runstatement(param).

Thanks for each reply. I'm new in database programming especially PostgreSQL.

Answer 1

This technique is named dynamic SQL. PLpgSQL has EXECUTE statement for this case. Probably is useless to write special function just for this, because EXECUTE is one line command.

CREATE OR REPLACE FUNCTION runstatement(statement TEXT)
RETURNS void AS $$
BEGIN
  IF statement <> '' THEN
    EXECUTE statement;
  END IF;
END;
$$ LANGUAGE plpgsql;

Probably test on empty string is bad design. This case should not to be. Assert is better there.

This function can be called:

SELECT runstatement(''); -- outside plpgsql

or

PERFORM runstatement('') -- inside plpgsql

See related part in documentation.