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

    Unanswered: Create a temporary table in a function

    I have a function where it selects into a temporary table from a table in the database and then uses the temporary table in another query.
    From my research this is not possible so I am wanting to know if anyone can help me out in suggesting the best way for me to go about writing this function. I think the only way is to create one complex sql query however if anyone can point me in the right direction with the right way to go about doing this, that would be great.

    Thanks
    Liam
    Heres the function.
    Code:
    -- Scheme Project Member query - Custom Query for PAA PI Scheme Project
    -- Gets all Members with submitted proposals who selected the a DIFFERENT level of PI Cover in the proposal for this renewal to last year.
    --NOTE: this excludes members who did not submit a proposal last year
    
    CREATE OR REPLACE FUNCTION PAA_sp_GetSchemeMemberSubmittedProposals_DifferentRenewalLimit
    (
    	int
    )
    RETURNS TABLE(MemberID int,
    	c__Previous_Option__Integer int,
    	c__New_Option__Integer int) 
    AS
    $$
    DECLARE RenewFromSchemeProjID int;
    
    BEGIN
    --First, get the SchemeProjecttID this one Renewed from
    
    SELECT RenewFromSchemeProjectID 
    INTO 
    RenewFromSchemeProjID
    
    FROM tblSchemeProject WHERE SchemeProjectID = $1;
    
    IF RenewFromSchemeProjID IS NOT NULL
    
    THEN
    
    	--This is a renewal project, get members with different limits selected between these schemes
    
    	--First get a temp table with the Renewed-From data (to join in the main SELECT query - simplifies the main query)
    
    
    
    
    	CREATE TEMPORARY TABLE PreviousProject AS
    
    	SELECT 
    		MemberID,  
    		tblProposal.ProposalID,
    		fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int) AS PreviousOption
    	
    	FROM 
    		tblSchemeProjectMember 
    		INNER JOIN tblProposal ON tblSchemeProjectMember.ProposalID = tblProposal.ProposalID
    	WHERE
    		tblSchemeProjectMember.SchemeProjectID = $1 AND 
    	
    		--Proposal is submitted
    		tblProposal.ProposalSubmitted IS NOT NULL;
    
    	
    RETURN QUERY
    
    	--Now get the Scheme Project Members from The current Scheme Project who:
    	--   1. Submitted a Proposal in both the Renewed-From Project AND the current Project; and
    	--   2. Selected a DIFFERENT cover level for the proposals in the Renewed-From Project and the current Project
    
    	SELECT 
    		-- The following fields are required in all Scheme Project Member queries:
    
    		tblSchemeProjectMember.MemberID,
    	
    		-- Custom columns:
    
    		PreviousProject.PreviousOption AS c__Previous_Option__Int,
    		fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int) AS c__New_Option__In
    
    	FROM	
    		tblSchemeProjectMember 
    		INNER JOIN tblProposal 
    		ON tblProposal.ProposalID = tblSchemeProjectMember.ProposalID 
    		INNER JOIN PreviousProject 
    		ON tblSchemeProjectMember.MemberID = PreviousProject.MemberID
    
    	WHERE 
    		tblSchemeProjectMember.SchemeProjectID = $1 AND 
    	
    		--Proposal is submitted and 1million PI Cover Level option is selected
    
    		tblProposal.ProposalSubmitted IS NOT NULL AND 
    
    		PreviousOption <> fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int);
    
    	DROP TABLE PreviousProject;
    END IF;
    END;
    $$Language 'plpgsql';

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Why are you so keen on creating temporary tables?

    Simply create one query out of that:
    Code:
    RETURN QUERY
    SELECT tblSchemeProjectMember.MemberID,
           PreviousProject.PreviousOption AS c__Previous_Option__Int,
           fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int) AS c__New_Option__In
    FROM  tblSchemeProjectMember 
      INNER JOIN tblProposal ON tblProposal.ProposalID = tblSchemeProjectMember.ProposalID 
      
      INNER JOIN (
      
        SELECT MemberID,  
               tblProposal.ProposalID,
               fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int) AS PreviousOption
        FROM tblSchemeProjectMember 
        INNER JOIN tblProposal ON tblProposal.ProposalID = tblSchemeProjectMember.ProposalID
        WHERE tblSchemeProjectMember.SchemeProjectID = $1 
         AND tblProposal.ProposalSubmitted IS NOT NULL  
    	 
      ) PreviousProject ON tblSchemeProjectMember.MemberID = PreviousProject.MemberID
      
    WHERE tblSchemeProjectMember.SchemeProjectID = $1 
      AND tblProposal.ProposalSubmitted IS NOT NULL 
      AND PreviousOption <> fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int);
    You might need to prefix some columns with the actual table names.

    But why do you need the temporary table at all. As far as I can tell the outer query is doing exactly the same thing as the inner query, why don't you use a simple self-join on the table then?

    But I might be missing something, I didn't look that closely into your statements.

  3. #3
    Join Date
    Oct 2009
    Posts
    37
    I have about 15 Ms SQL store procedures that all use a temporary table like this. I think the only reason why was to make it easier when writing them instead of having to put alot of thought in to constructing an sql query.

    As for the above query the two select statements are doing similar things however they are using different parameters.
    I have actually posted the function with the wrong parameter in the first query.
    Heres how it should look using the parameter "RenewFromSchemeProjID", which I think will make having one query more complicated.

    Code:
    -- Scheme Project Member query - Custom Query for PAA PI Scheme Project
    -- Gets all Members with submitted proposals who selected the a DIFFERENT level of PI Cover in the proposal for this renewal to last year.
    --NOTE: this excludes members who did not submit a proposal last year
    
    CREATE OR REPLACE FUNCTION PAA_sp_GetSchemeMemberSubmittedProposals_DifferentRenewalLimit
    (
    	schemeprojid int
    )
    RETURNS TABLE(MemberID int,
    	c__Previous_Option__Integer int,
    	c__New_Option__Integer int) 
    AS
    $$
    DECLARE RenewFromSchemeProjID int;
    
    BEGIN
    --First, get the SchemeProjecttID this one Renewed from
    
    SELECT RenewFromSchemeProjectID 
    INTO 
    RenewFromSchemeProjID
    
    FROM tblSchemeProject WHERE SchemeProjectID = $1;
    
    IF RenewFromSchemeProjID IS NOT NULL
    
    THEN
    
    	--This is a renewal project, get members with different limits selected between these schemes
    
    	--First get a temp table with the Renewed-From data (to join in the main SELECT query - simplifies the main query)
    
    
    
    
    	CREATE TEMPORARY TABLE PreviousProject AS
    
    	SELECT 
    		MemberID,  
    		tblProposal.ProposalID,
    		fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int) AS PreviousOption
    	
    	FROM 
    		tblSchemeProjectMember 
    		INNER JOIN tblProposal ON tblSchemeProjectMember.ProposalID = tblProposal.ProposalID
    	WHERE
    		tblSchemeProjectMember.SchemeProjectID = RenewFromSchemeProjID AND 
    	
    		--Proposal is submitted
    		tblProposal.ProposalSubmitted IS NOT NULL;
    
    RETURN QUERY	 
    
    	--Now get the Scheme Project Members from The current Scheme Project who:
    	--   1. Submitted a Proposal in both the Renewed-From Project AND the current Project; and
    	--   2. Selected a DIFFERENT cover level for the proposals in the Renewed-From Project and the current Project
    
    	SELECT 
    		-- The following fields are required in all Scheme Project Member queries:
    
    		tblSchemeProjectMember.MemberID,
    	
    		-- Custom columns:
    
    		PreviousProject.PreviousOption AS c__Previous_Option__Int,
    		fn_GetPropData_Integer(''PILevelOfCover'', tblProposal.ProposalID::int) AS c__New_Option__In
    
    	FROM	
    		tblSchemeProjectMember 
    		INNER JOIN tblProposal 
    		ON tblProposal.ProposalID = tblSchemeProjectMember.ProposalID 
    		INNER JOIN PreviousProject 
    		ON tblSchemeProjectMember.MemberID = PreviousProject.MemberID
    
    	WHERE 
    		tblSchemeProjectMember.SchemeProjectID = $1 AND 
    	
    		--Proposal is submitted and 1million PI Cover Level option is selected
    
    		tblProposal.ProposalSubmitted IS NOT NULL AND 
    
    		PreviousOption <> fn_GetPropData_Integer(''PILevelOfCover'', tblProposal.ProposalID::int);
    
    	DROP TABLE PreviousProject;
    END IF;
    END;
    $$Language 'plpgsql';

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by tcliam View Post
    I have about 15 Ms SQL store procedures that all use a temporary table like this.
    Strange strategy...

    As for the above query the two select statements are doing similar things however they are using different parameters.
    Makes more sense

    I have actually posted the function with the wrong parameter in the first query.
    Heres how it should look using the parameter "RenewFromSchemeProjID", which I think will make having one query more complicated.
    Not at all, just move the query for the temp table inside the main query as shown in my first example.

    Temp tables might be a good choice for MS SQL Server, but they are not for Postgres (or Oracle that is)

  5. #5
    Join Date
    Oct 2009
    Posts
    37
    Not at all, just move the query for the temp table inside the main query as shown in my first example.
    Yes I did this but I am getting an error saying PreviousProject.MemberID doesn't exist. This is something that I have come a accross a number of times when using RETURN QUERY and assigning a name to a table like "PreviousProject". It always seems to say that the table doesn't exist.
    If anyone knows why this would happen please let me know?

    Thanks
    Liam

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by tcliam View Post
    Yes I did this but I am getting an error saying PreviousProject.MemberID doesn't exist. This is something that I have come a accross a number of times when using RETURN QUERY and assigning a name to a table like "PreviousProject". It always seems to say that the table doesn't exist.
    If anyone knows why this would happen please let me know?
    If you care to show us the statement that generates the error?
    Sounds like something is wrong with the aliasing.

  7. #7
    Join Date
    Oct 2009
    Posts
    37
    The error is from this piece of code.
    Code:
      INNER JOIN (
      
        SELECT MemberID,  
               tblProposal.ProposalID,
               fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int) AS PreviousOption
        FROM tblSchemeProjectMember 
    
        INNER JOIN tblProposal ON tblProposal.ProposalID = tblSchemeProjectMember.ProposalID
        WHERE tblSchemeProjectMember.SchemeProjectID = RenewFromSchemeProjID
         AND tblProposal.ProposalSubmitted IS NOT NULL  
    	 
      ) PreviousProject ON tblSchemeProjectMember.MemberID = PreviousProject.MemberID
    It seems to crash on
    Code:
     PreviousProject ON tblSchemeProjectMember.MemberID = PreviousProject.MemberID
    And this is the error message
    Code:
    ERROR:  column previousproject.memberid does not exist
    LINE 1: ...viousProject ON tblSchemeProjectMember.MemberID = PreviousPr...
    Thanks
    Liam

  8. #8
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Given your MS background, I'm guessing that you created your tables quoted and in camelcase. If you did that, you'll need to continue to quote and camelcase your columns.

    We shy away from quote and camelcasing in Postgres.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You could also try to

    a) fully qualify the column names in the innner query (avoid mixing unqualified and qualified column names in a query)

    b) give the column a dedicated alias (member_id_table.MemberID as memberid)

  10. #10
    Join Date
    Oct 2009
    Posts
    37
    Given your MS background, I'm guessing that you created your tables quoted and in camelcase. If you did that, you'll need to continue to quote and camelcase your columns.
    Originally this was the case however I found it too time consuming to be quoting everying when writing functions so I have done everything in lowercase.

    I will give these suggestions a try Shammat.

    Thanks again to both of you.

    Liam

  11. #11
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    A few months ago someone had read an article that had a script to fix column names that had been created like this.
    Lowercasing table and column names - Postgres OnLine Journal

Posting Permissions

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