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
PS I use PostgreSQL v. 8.3 and I cannot upgrade.