Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    37

    Unanswered: plpgsql function select, update then return

    Hi

    I just cannot seem to work out how to achieve this. What I want to do is select some data then run an update command and return the original data after that.

    It seems like a simple task but I haven't been able to find the correct techniques for doing so. Here is my attempt below. It crashes saying no destination for result data.

    Thanks in advance.

    Code:
    CREATE OR REPLACE FUNCTION sp_getnewsections(propid integer)
      RETURNS SETOF character varying AS
    $$
    DECLARE row_c int;
    
    BEGIN
    
    SELECT     
    SectionTitle
    
    FROM         
    viewProposalSection
    WHERE     (ShowSection = TRUE) 
    AND (ShowSectionPrevious = FALSE) 
    AND (ProposalID = propid);
    
    GET DIAGNOSTICS row_c = ROW_COUNT;
    
    IF row_c > 0
    THEN	
    	UPDATE    tblProposalSection
    	SET              ShowSectionPrevious = ShowSection
    	WHERE     (ProposalID = propid);
    END IF;
    
    END;
    
    $$LANGUAGE 'plpgsql' VOLATILE;

  2. #2
    Join Date
    Oct 2009
    Posts
    37
    OK I managed to get what appears to be a working solution by selecting in to an array and then returning the array at the end.

    Code:
    CREATE OR REPLACE FUNCTION sp_getnewsections(IN propid integer)
      RETURNS SETOF character varying AS
    $$
    DECLARE row_c int;
    retVars  character varying[] := ARRAY(SELECT     
    SectionTitle
    
    FROM         
    viewProposalSection
    
    WHERE     (ShowSection = TRUE) 
    AND (ShowSectionPrevious = FALSE) 
    AND (ProposalID = propid));
    
    BEGIN
    
    row_c = array_upper(retvars, 1);
    
    IF row_c > 0
    	THEN	
    		UPDATE    tblProposalSection
    		SET              ShowSectionPrevious = ShowSection
    		WHERE     (ProposalID = propid);
    END IF;
    
    
    RETURN QUERY SELECT e FROM unnest(retVars) e;
    END;
    
    $$LANGUAGE 'plpgsql' VOLATILE;

Posting Permissions

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