Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Question Unanswered: Update/cursors question...

    Hi guys,

    I have had a look on the forums @ postgresql.org but couldn't find what I wanted;

    I want to do;
    Code:
    create sequence temp;
    
    UPDATE x
    SET x.order_sequence=nextval(temp)
    WHERE x.blah=44
    ORDER BY x.order_sequence ASC;
    
    drop sequence temp;
    to explain why I need this, x.order_sequence is a column for allowing a user to order rows on the front end.

    Problem which this is trying to solve is that if a row is removed there is a gap in the order_sequence ---> i.e. 1,2,3,5,6,7,8. I can't have this...

    Anyway - its the order by clause on the update column which I don't know A) if its possible B) how else to do it


    Thanks!
    Last edited by rhs98; 11-13-03 at 06:05.

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Cursors maybe?

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    trying this; but can't find info on cursor iteration -

    Code:
    -- Function: public.p_update_order_sequence(int4)
    
    -- DROP FUNCTION public.p_update_order_sequence(int4);
    
    CREATE OR REPLACE FUNCTION public.p_update_order_sequence(int4)
      RETURNS SETOF jk_p_q_questions AS
    '
    	DECLARE
    		QUESTIONNAIRE_id ALIAS FOR $1;
    		qql CURSOR (key int) IS SELECT id from q_q_l where q_id = key ORDER BY order_sequence ASC;
    
    	BEGIN
    		--create a temporary sequence to renumber the order_sequence
    		CREATE SEQUENCE temp;
    
    		--Open the cursor
    		OPEN qql(QUESTIONNAIRE_id);
    
    Don't know how to, but need to loop this bit in bold
    
    		FETCH qql INTO qql_id;
    
    		--should update one row...
    		UPDATE q_q_l
    		SET order_sequence=NEXTVAL(temp)
    		WHERE id=qql_id;
    
    		
    		CLOSE qql;
    		--drop the temporary sequence
    		DROP SEQUENCE temp;
    		
    	END;
    '
      LANGUAGE 'plpgsql' STABLE;

  4. #4
    Join Date
    Nov 2003
    Location
    Poland
    Posts
    7
    instead of cursor you can use the record or row type and loop through the result of the "SELECT id from q_q_l where q_id = key ORDER BY order_sequence ASC;"

    declare
    rec record;
    begin

    for rec in SELECT id from q_q_l where q_id = key ORDER BY order_sequence ASC loop

    update whatever you want;
    end loop;




    Originally posted by rhs98
    trying this; but can't find info on cursor iteration -

    Code:
    -- Function: public.p_update_order_sequence(int4)
    
    -- DROP FUNCTION public.p_update_order_sequence(int4);
    
    CREATE OR REPLACE FUNCTION public.p_update_order_sequence(int4)
      RETURNS SETOF jk_p_q_questions AS
    '
    	DECLARE
    		QUESTIONNAIRE_id ALIAS FOR $1;
    		qql CURSOR (key int) IS SELECT id from q_q_l where q_id = key ORDER BY order_sequence ASC;
    
    	BEGIN
    		--create a temporary sequence to renumber the order_sequence
    		CREATE SEQUENCE temp;
    
    		--Open the cursor
    		OPEN qql(QUESTIONNAIRE_id);
    
    Don't know how to, but need to loop this bit in bold
    
    		FETCH qql INTO qql_id;
    
    		--should update one row...
    		UPDATE q_q_l
    		SET order_sequence=NEXTVAL(temp)
    		WHERE id=qql_id;
    
    		
    		CLOSE qql;
    		--drop the temporary sequence
    		DROP SEQUENCE temp;
    		
    	END;
    '
      LANGUAGE 'plpgsql' STABLE;

Posting Permissions

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