Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    15

    Red face Unanswered: CREATE OR REPLACE RULE my_view

    Greetings!
    I'm in some desperate need of help creating a rule on a view.

    I've created a view:
    Code:
    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:
    Code:
    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:
    Code:
    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.

  2. #2
    Join Date
    Nov 2006
    Posts
    82
    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 ?

  3. #3
    Join Date
    Aug 2005
    Posts
    15
    ... tak, dzieki ;-)

  4. #4
    Join Date
    Aug 2005
    Posts
    15
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •