Create a temporary table in a function

tcliam · #1 (**permalink**) 01-21-10, 16:56

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';

shammat · #2 (**permalink**) 01-21-10, 17:17

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.

tcliam · #3 (**permalink**) 01-21-10, 17:29

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';

shammat · #4 (**permalink**) 01-21-10, 17:51

Quote:

Originally Posted by tcliam

I have about 15 Ms SQL store procedures that all use a temporary table like this.

Strange strategy...

Quote:

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

Makes more sense

Quote:

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)

tcliam · #5 (**permalink**) 01-21-10, 21:43

Quote:

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

shammat · #6 (**permalink**) 01-22-10, 02:35

Quote:

Originally Posted by tcliam

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.

tcliam · #7 (**permalink**) 01-26-10, 15:56

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

artacus72 · #8 (**permalink**) 01-27-10, 11: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.

We shy away from quote and camelcasing in Postgres.

shammat · #9 (**permalink**) 01-27-10, 12:08

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)

tcliam · #10 (**permalink**) 01-27-10, 14:40

Quote:

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

artacus72 · #11 (**permalink**) 01-28-10, 11:22

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