[dbtomek]

Greetings!
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.

Thank you in advance.

[rski]

Maybe it's because u write

set roasters_grinders.roasters_grinders_id = '-1'

change the definition to
CREATE OR REPLACE RULE update_my_view AS ON
SELECT TO init_contact_view
DO instead update roasters_grinders
SET roasters_grinders_id = '-1'
WHERE roasters_grinders_id IS NULL;

PS
Jestes z Polski ?

[dbtomek]

... tak, dzieki ;-)

[dbtomek]

I got it to work, but silly me, all I really needed was the coalesce() function when selecting from the view and creating a join:

coalesce ( roasters_grinders_id, -1 ) as roasters_grinders_id,

... so I get a 'temporary' -1 instead of a null.
t