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.

4
  • 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. Jul 7, 2016 at 11:03
  • I cannot upgrade to newer version due to customer's requirements...
    – flaz14
    Jul 7, 2016 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. Jul 7, 2016 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. Jul 7, 2016 at 12:40

1 Answer 1

2

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 and acknowledge you have read our privacy policy.

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