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

    Unanswered: Function with two Select statements

    Hi guys, I'm writing the following Function that contains two Select statements but only returns columns from the second Select statement. I think that this is the reason I am getting this error.

    ERROR: query has no destination for result data
    HINT: If you want to discard the results of a SELECT, user PERFORM instead.
    CONTEXT PL/pgSQL function "foo" line 6 at SQL statement


    Heres the first part of the function containing the first Select statement which is only used to set the "accntid" parameter.

    Code:
    CREATE OR REPLACE FUNCTION foo(userid int8)
    
    RETURNS TABLE(SchemeID integer, SchemeName text)  
    AS
    $$
    DECLARE 
    
    acntid integer;
    
    BEGIN
    
    SELECT
    	acntid = AccountID 
    FROM 
    	public.tblUser
    WHERE 
    	tblUser.userid = $1;
    And the rest of the code containing the second Select statement follows straight after.

    Code:
    SELECT DISTINCT 
    	tblScheme.SchemeID, 
    	tblScheme.SchemeName
    
    FROM 
    	public.tblScheme
    
    INNER JOIN 
    public.tblSchemeProject ON tblScheme.SchemeID = tblSchemeProject.SchemeID
    
    INNER JOIN 
    public.tblSchemeProjectAccount ON tblSchemeProject.SchemeProjectID = tblSchemeProjectAccount.SchemeProjectID 
    
    LEFT OUTER JOIN 
    public.Scheme_tblProjectUser ON tblSchemeProject.SchemeProjectID = Scheme_tblProjectUser.SchemeProjectID AND Scheme_tblProjectUser.UserID = $1
    
    WHERE 
    	tblSchemeProjectAccount.AccountID = acntid 
    	AND (tblSchemeProjectAccount.RestrictedUsers = FALSE OR (tblSchemeProjectAccount.RestrictedUsers= TRUE AND Scheme_tblProjectUser.UserID IS NOT NULL))
    
    
    ORDER BY 
    	tblScheme.SchemeName;
    END;
    $$LANGUAGE 'plpgsql';
    If anyone knows how to work around this that would be great have been trying to work this out for hours.

    Thanks
    Liam

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Weren't you the one we told to use SELECT INTO last week? The same holds true this week.

    Code:
    SELECT AccountID INTO acntid 
    FROM public.tblUser
    WHERE tblUser.userid = $1;

  3. #3
    Join Date
    Oct 2009
    Posts
    37
    Weren't you the one we told to use SELECT INTO last week? The same holds true this week.
    Hi I have changed this and it doesn't fix the error?

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Post the code. How many records are you expecting the second query to return?

  5. #5
    Join Date
    Oct 2009
    Posts
    37
    I worked it out, after each SELECT statement you need to write "Return;"

    Thanks.

    Code:
    CREATE OR REPLACE FUNCTION user_getschemes(userid int8)
    
    RETURNS TABLE("SchemeID" integer, "SchemeName" text)  
    AS
    $$
    DECLARE 
    
    acntid integer;
    
    BEGIN
    
    SELECT
    	"tblUser"."AccountID" INTO acntid
    FROM 
    	public."tblUser"
    WHERE 
    	"tblUser"."userid" = $1;
    RETURN;

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    That will only run the first half of your function.

  7. #7
    Join Date
    Oct 2009
    Posts
    37
    That will only run the first half of your function.
    Yes. Will "Return Next;" work then?

  8. #8
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Ok, I'm tired of teaching you how to fish. So here is a fish. Be sure to check it over before using it.

    Code:
    CREATE FUNCTION FOO(
      userid   IN  int8
    ) RETURNS TABLE(schemaID  int, schemaName text) AS
    $$
      SELECT s.schemeID, s.schemeName
      FROM tblScheme s
      JOIN tblSchemeProject proj ON s.schemeID = proj.schemeID
      JOIN tblUser u ON u.userID = $1
      JOIN tblSchemeProjectAccount acct ON proj.schemeProjectID = acct.schemeProjectID
        AND u.accountID = acct.accountID
      LEFT JOIN scheme_tblProjectUser pUser ON proj.schemeProjectID = pUser.schemeProjectID
        AND pUser.userID = u.userID
      WHERE acct.restrictedUsers = false 
        OR acct.userID IS NOT NULL
      ORDER BY s.schemeName;
    $$ LANGUAGE 'sql' VOLATILE;

  9. #9
    Join Date
    Oct 2009
    Posts
    37
    Ok, I'm tired of teaching you how to fish. So here is a fish. Be sure to check it over before using it.
    Thanks for your reply. I'm converting all of these functions from T-SQL so I'm still trying to grasp all of the differences between the two.

  10. #10
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I'm converting all of these functions from T-SQL
    Yeah, we can pretty much tell what databases people are coming from by they style of code they write, especially with MS SQL.

    For simple functions like this were we don't need to walk cursors, you should try to do it as a sql query. Hard code the params in and test it. Once you've got it working just wrap it in a 'sql' (notice I didn't use plpgsql) function and replace the params.

  11. #11
    Join Date
    Oct 2009
    Posts
    37
    (notice I didn't use plpgsql)
    Yes I did notice that. Thanks for the tip will try doing this with the other functions I have like this one.

  12. #12
    Join Date
    Dec 2009
    Posts
    2

    answer to your query is:"

    Write ur this code
    "SELECT
    acntid = AccountID
    FROM
    public.tblUser
    WHERE
    tblUser.userid = $1" not as this
    .................................... try this...........
    SELECT
    into acntid AccountID
    FROM
    public.tblUser
    WHERE
    tblUser.userid = $1


    ---
    regards,
    Sachin

    --------------------------------------------------------------------------



    ---
    Quote Originally Posted by tcliam View Post


    Hi guys, I'm writing the following Function that contains two Select statements but only returns columns from the second Select statement. I think that this is the reason I am getting this error.

    ERROR: query has no destination for result data
    HINT: If you want to discard the results of a SELECT, user PERFORM instead.
    CONTEXT PL/pgSQL function "foo" line 6 at SQL statement


    Heres the first part of the function containing the first Select statement which is only used to set the "accntid" parameter.

    Code:
    CREATE OR REPLACE FUNCTION foo(userid int8)
    
    RETURNS TABLE(SchemeID integer, SchemeName text)  
    AS
    $$
    DECLARE 
    
    acntid integer;
    
    BEGIN
    
    SELECT
    	acntid = AccountID 
    FROM 
    	public.tblUser
    WHERE 
    	tblUser.userid = $1;
    And the rest of the code containing the second Select statement follows straight after.

    Code:
    SELECT DISTINCT 
    	tblScheme.SchemeID, 
    	tblScheme.SchemeName
    
    FROM 
    	public.tblScheme
    
    INNER JOIN 
    public.tblSchemeProject ON tblScheme.SchemeID = tblSchemeProject.SchemeID
    
    INNER JOIN 
    public.tblSchemeProjectAccount ON tblSchemeProject.SchemeProjectID = tblSchemeProjectAccount.SchemeProjectID 
    
    LEFT OUTER JOIN 
    public.Scheme_tblProjectUser ON tblSchemeProject.SchemeProjectID = Scheme_tblProjectUser.SchemeProjectID AND Scheme_tblProjectUser.UserID = $1
    
    WHERE 
    	tblSchemeProjectAccount.AccountID = acntid 
    	AND (tblSchemeProjectAccount.RestrictedUsers = FALSE OR (tblSchemeProjectAccount.RestrictedUsers= TRUE AND Scheme_tblProjectUser.UserID IS NOT NULL))
    
    
    ORDER BY 
    	tblScheme.SchemeName;
    END;
    $$LANGUAGE 'plpgsql';
    If anyone knows how to work around this that would be great have been trying to work this out for hours.

    Thanks
    Liam

Posting Permissions

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