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.