PostgreSQL - cannot create RULE from stored procedure

Question

I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue when creating rule from function implemented in PL/pgSQL.

Here is an excerpt from SQL script for rolling out changes:

CREATE TABLE person_x (
  firstname TEXT NOT NULL,
  lastname  TEXT
);

CREATE TABLE person_y (
  firstname TEXT NOT NULL,
  lastname  TEXT
);


CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
  RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
BEGIN
  CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
  DO INSTEAD
    INSERT INTO person_y (
      firstname,
      lastname)
    VALUES (
      new.firstname,
      new.lastname
    );

  RETURN 1;
END;
$test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;

But I get:

psql:rollout.sql:348: ERROR:  42601: syntax error at "person_y"
DETAIL:  Expected record variable, row variable, or list of scalar variables following INTO.
CONTEXT:  compile of PL/pgSQL function "test_create_rule_from_stored_procedure" near line 4
LOCATION:  read_into_target, gram.y:2322

But when I run query for creating rule (the query is the same as in function) directly from SQL script everything is fine.

Is it possible to create rules from stored procedures? Or I need to compose query for creating rule as string and then execute it with aid of EXECUTE?

PS I use PostgreSQL v. 8.3 and I cannot upgrade.

Answer 1

Thank you everyone for suggestions in comments. I've found out two solutions.

1. Use SQL stored procedure instead of PL/pgSQL:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS VOID AS $test_create_rule_from_stored_procedure$
    CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
    DO INSTEAD
      INSERT INTO person_y (
        firstname,
        lastname)
      VALUES (
        new.firstname,
        new.lastname
      );
    $test_create_rule_from_stored_procedure$ LANGUAGE SQL;

2. Stay with PL/pgSQL but specify query for creating rule as string literal:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
    BEGIN
      EXECUTE 'CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
      DO INSTEAD
        INSERT INTO person_y (
          firstname,
          lastname)
        VALUES (
          new.firstname,
          new.lastname
        );';

      RETURN 1;
    END;
    $test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;