1

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.

  • 1
    If that was a bug in 8.3.x, it's fixed in recent versions. 8.3 has been EOL'ed in Feb 2013, you should upgrade. – Daniel Vérité Jul 7 '16 at 11:03
  • I cannot upgrade to newer version due to customer's requirements... – flaz14 Jul 7 '16 at 12:08
  • 1
    At least make sure it's the latest in the branch, 8.3.23. If it doesn't work with the latest, the workaround you mentioned with execute looks like the easiest way to avoid the problem. – Daniel Vérité Jul 7 '16 at 12:39
  • 1
    Or maybe make it a SQL function instead of plpgsql, as it has no procedural logic and the return 1 is really not necessary. – Daniel Vérité Jul 7 '16 at 12:40
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;
  1. 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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.