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.
return 1
is really not necessary.