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

    Unanswered: MSSQL SP to Postgresql Function

    Hi guys

    I have a MSS SQL stored procedure that I need to convert to a postgresql function. I have come accross a problem to do with declaring a value then selecting in to that value, then doing another select query. I usually counter this by using a join but this time the value being declared is used in a subquery. I will paste the MS SQL stored procedure code below, the value that I am referring to is the "CountryID", if anyone can see a workaround to my proplem I would be very greatful. I will show what I have so far in postgres aswell.
    Thanks
    Liam
    MS SQL
    Code:
    CREATE PROCEDURE foo
    (
    	@AccountID int -- the Broker
    )
    
    AS 
    
    DECLARE @CountryID int
    SELECT 
    	@CountryID = CountryID 
    FROM 
    	tblAccount 
    WHERE 
    	AccountID = @AccountID
    
    SELECT 
    	tblAccount.AccountID, 
    	tblAccount.AccountCompanyName, 
    	tblAccount.SmallLogoID
    
    FROM 
    	tblAccount 
    
    WHERE 
    	tblAccount.AccountTypeID = 10 AND 
    	tblAccount.AccountID IN 
    
    		(SELECT
    			AccountID 
    		FROM  
    			tblAccountCountryInsurerPresence 
    		WHERE 
    			CountryID = @CountryID 
    
    		UNION 
    
    		SELECT 
    			InsurerAccountID 
    		FROM 
    			tblBrokerInsurerAccount 
    		WHERE 
    			BrokerAccountID = @AccountID)
    GO
    Postgresql function
    Code:
    CREATE OR REPLACE FUNCTION foo(AccountID int )
    RETURNS TABLE (accountid int, accountcompanyname character varying, smalllogoid int)
    AS 
    $$
    
    
    SELECT 
    	acc.AccountID, 
    	acc.AccountCompanyName, 
    	acc.SmallLogoID
    
    FROM 
    	public.tblAccount acc
    
    
    WHERE 
    	acc.AccountTypeID = 10 AND 
    	acc.AccountID IN 
    
    		(
    		SELECT
    			AccountID 
    		FROM  
    			public.tblAccountCountryInsurerPresence 
    		WHERE 
    			CountryID = ??
    
    		UNION 
    
    		SELECT 
    			InsurerAccountID 
    		FROM 
    			public.tblBrokerInsurerAccount 
    		WHERE 
    			BrokerAccountID = $1);
    $$Language 'sql' VOLATILE;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    To populate a variable from a SELECT statement use the INTO keyword.

    As far as I can tell the equivalent would be:
    Code:
    CREATE OR REPLACE FUNCTION foo(AccountID int )
    RETURNS TABLE (accountid int, accountcompanyname character varying, smalllogoid int)
    AS 
    $$
    
    DECLARE country_id int;
    
    BEGIN 
        SELECT CountryID 
        INTO country_id
        FROM tblAccount 
        WHERE AccountID = accountid
    
        SELECT 
            acc.AccountID, 
            acc.AccountCompanyName, 
            acc.SmallLogoID
        FROM 
            public.tblAccount acc
        WHERE 
            acc.AccountTypeID = 10 AND 
            acc.AccountID IN 
            (
                SELECT
                    AccountID 
                FROM  
                    public.tblAccountCountryInsurerPresence 
                WHERE 
                    CountryID = country_id
    
                UNION 
    
                SELECT 
                    InsurerAccountID 
                FROM 
                    public.tblBrokerInsurerAccount 
                WHERE 
                    BrokerAccountID = $1
            );
    END
            
    $$Language 'sql' VOLATILE;

  3. #3
    Join Date
    Oct 2009
    Posts
    37
    Hi shammat will try this however I'm fairly positive that it won't work as this is the usual problem I face and have to use a join to work around. When using two select statements like this postgresql expects the value being selected to be returned. Because it isn't and is only being used for storing a temporary value an error is returned along the lines of "no destination for result data".
    If you have any other ideas that would be great.

    Thanks
    Liam

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by tcliam View Post
    Hi shammat will try this however I'm fairly positive that it won't work as this is the usual problem I face and have to use a join to work around. When using two select statements like this postgresql expects the value being selected to be returned. Because it isn't and is only being used for storing a temporary value an error is returned along the lines of "no destination for result data".
    If you have any other ideas that would be great.
    Silly me. I just noticed that you are not using PL/pgSQL but "plain" SQL.
    Well variable declaration and assignment is only supported with PL/pgSQL.

    So my example is actually wrong, you need to change

    $$Language 'sql' VOLATILE;

    to

    $$Language 'plpsql' VOLATILE;

    and then storing a value into a variable will work. But you need to make sure that the query only returns a single value/row.

    Sorry I didn't spot that right away.
    I always forget that you can write a function with "just" SQL as well

    You might need to change the syntax when returning a table (not 100% if that is working just like that in PL/pgSQL). But the manual should help you there:

    http://www.postgresql.org/docs/current/static/plpgsql.html

  5. #5
    Join Date
    Oct 2009
    Posts
    37
    Off the top of my head it's returning more than one row, however the problem is still there as it is only returning a selection of columns from the tables row. And yes you can still return a table like this in plpgsql.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    This works for me:
    Code:
    CREATE OR REPLACE FUNCTION my_test(some_input varchar)
    RETURNS TABLE (result_col_one integer, result_col_two varchar(75))
    AS
    $$
    DECLARE local_var int;
    BEGIN
    
      SELECT some_column
      INTO local_var
      FROM first_table
      WHERE some_other_col = $1;
      
       RETURN QUERY 
         SELECT first_col, second_col
         FROM other_table
         WHERE some_col = some_var;
    end;
    $$ language plpgsql;
    Off the top of my head it's returning more than one row
    Then I'm pretty certain it wouldn't have worked in SQL Server as well.

  7. #7
    Join Date
    Oct 2009
    Posts
    37
    Ok thanks for this will give it a go tomorrow.

  8. #8
    Join Date
    Oct 2009
    Posts
    37
    It works! Thanks so much Shammat this is going to be seriously helpful for me.

Posting Permissions

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