I'm in some desperate need of help creating a rule on a view.
I've created a view:
CREATE VIEW init_contact_view AS SELECT * FROM init_contact
-- roaster_grinders is has only 1 col: 'roasters_grinders_id' bigint
LEFT OUTER JOIN roasters_grinders
ON init_contact.contact_id = roasters_grinders.roasters_grinders_id;
... selecting from it works fine:
SELECT * FROM init_contact_view ORDER BY contact_id;
... here's where the problems starts, sometimes the view contains nulls in the 'roasters_grinders_id' col and I need to have bigint of -1 value instead.
So I'm trying to replace all nulls with bigints and I've tried several times to create a rule for, but keep screwing something up. Yes, I'm very new at this and I'd really appreciate any feedback/help.
Here's where I've given up for the moment:
CREATE OR REPLACE RULE update_my_view AS ON
SELECT TO init_contact_view
DO instead update
SET roasters_grinders.roasters_grinders_id = '-1'
WHERE roasters_grinders_id IS NULL;
It gives th is: ERROR: syntax error at or near "roasters_grinders"
Yes, I'm very new at this and I'd really appreciate any feedback/help.