Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2016
    Posts
    18
    Provided Answers: 1

    Answered: PostgreSQL 9.3 I function returnig data from 2 tables

    Hello,
    In PostgreSQL 9.3 I wrote function returnig data from pd_product_comment table + 1 joined field username :
    CREATE FUNCTION get_new_product_comments() RETURNS SETOF pd_product_comment
    LANGUAGE plpgsql
    AS $$
    declare
    r pd_product_comment%rowtype;
    begin
    for r in
    SELECT pd_product_comment.*, pd_ion_users.username as username, pd_ion_users.username as username
    FROM pd_product_comment
    LEFT JOIN pd_ion_users ON pd_ion_users.id = pd_product_comment.user_id
    WHERE pd_product_comment.approved_status = 'N'
    GROUP by pd_product_comment.id, pd_product_comment.product_id, username
    ORDER BY pd_product_comment.product_id, pd_product_comment.created_at asc
    loop
    return next r;
    end loop;
    return;
    end
    $$;
    But I got only fileds of pd_product_comment table, not username field...

    have I to create new Type for this? If yes, please give sample or link how to do it ?

    Thanks!

  2. Best Answer
    Posted by shammat

    "
    Quote Originally Posted by mstdmstd View Post
    But I got only fileds of pd_product_comment table, not username field
    Yes, because that's how your function is declared: "RETURNS SETOF pd_product_comment" and because your record "r" only contains columns from that table

    To define more columns then the table has you need change the result definition:
    Something like this:
    Code:
    CREATE FUNCTION get_new_product_comments() 
       RETURNS table (comment_id integer, approved_status varchar, username varchar, ...)
    Of course the exact number of columns and their data types depends on how your tables look like.

    Additionally: your function is needlessly complex. There is no need for a (slow) cursor. In fact you don't need PL/pgSQL at all.

    So the complete function would be:
    Code:
    CREATE FUNCTION get_new_product_comments() 
      RETURNS table (....) ---<<< CHANGE THIS to match your columns
    AS $$
      SELECT pd_product_comment.*, pd_ion_users.username as username, pd_ion_users.username as username
      FROM pd_product_comment
        LEFT JOIN pd_ion_users ON pd_ion_users.id = pd_product_comment.user_id
      WHERE pd_product_comment.approved_status = 'N'
      GROUP by pd_product_comment.id, pd_product_comment.product_id, username
      ORDER BY pd_product_comment.product_id, pd_product_comment.created_at asc;
    $$
    LANGUAGE sql;
    "


  3. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Quote Originally Posted by mstdmstd View Post
    But I got only fileds of pd_product_comment table, not username field
    Yes, because that's how your function is declared: "RETURNS SETOF pd_product_comment" and because your record "r" only contains columns from that table

    To define more columns then the table has you need change the result definition:
    Something like this:
    Code:
    CREATE FUNCTION get_new_product_comments() 
       RETURNS table (comment_id integer, approved_status varchar, username varchar, ...)
    Of course the exact number of columns and their data types depends on how your tables look like.

    Additionally: your function is needlessly complex. There is no need for a (slow) cursor. In fact you don't need PL/pgSQL at all.

    So the complete function would be:
    Code:
    CREATE FUNCTION get_new_product_comments() 
      RETURNS table (....) ---<<< CHANGE THIS to match your columns
    AS $$
      SELECT pd_product_comment.*, pd_ion_users.username as username, pd_ion_users.username as username
      FROM pd_product_comment
        LEFT JOIN pd_ion_users ON pd_ion_users.id = pd_product_comment.user_id
      WHERE pd_product_comment.approved_status = 'N'
      GROUP by pd_product_comment.id, pd_product_comment.product_id, username
      ORDER BY pd_product_comment.product_id, pd_product_comment.created_at asc;
    $$
    LANGUAGE sql;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Oct 2016
    Posts
    18
    Provided Answers: 1
    Thank you for your help!

    I remade my function as plpgsql:
    Code:
    CREATE FUNCTION pd_get_new_product_comments() RETURNS TABLE ( 	id int4, product_id int4, parent_product_comment_id int4 , comment_text text, approved_status type_approved_status, user_id int2 ,	rating int2, created_at timestamp )
        LANGUAGE plpgsql
        AS $$
      begin
           SELECT pd_product_comment.*, pd_ion_users.username as username, pd_ion_users.username as username
            FROM pd_product_comment
            LEFT JOIN pd_ion_users ON pd_ion_users.id = pd_product_comment.user_id
            WHERE pd_product_comment.approved_status = 'N'
            GROUP by pd_product_comment.id, pd_product_comment.product_id, username
            ORDER BY pd_product_comment.product_id, pd_product_comment.created_at asc;
      end
    $$;
    and running :
    Code:
    select * from pd_get_new_product_comments()
    I got

    Code:
    SQL Error [42601]: ERROR: query has no destination for result data
      Hint: If you want to discard the results of a SELECT, use PERFORM instead.
      Where: PL/pgSQL function pd_get_new_product_comments() line 3 at SQL statement
      org.postgresql.util.PSQLException: ERROR: query has no destination for result data
      Hint: If you want to discard the results of a SELECT, use PERFORM instead.
      Where: PL/pgSQL function pd_get_new_product_comments() line 3 at SQL statement
    which is the right way ?
    I am new in PostgreSQL so would prefer to work with PL/pgSQL, as I suppose it is more powerfull and if I would need to alter my function with plpgsql
    features later, maybe better to make all my functions in plpgsql from the start ?

  5. #4
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Please see my example, you do not need PL/pgSQL for this. A plain SQL function will be much more efficient.

    If you insist on doing it in a less-optimal way: you need "return query" if you want to return a result from within PL/pgSQL.

    Code:
    CREATE FUNCTION pd_get_new_product_comments() 
      RETURNS TABLE (id int4, product_id int4, parent_product_comment_id int4 , comment_text text, approved_status type_approved_status, user_id int2 ,	rating int2, created_at timestamp)
    LANGUAGE plpgsql
    AS $$
      begin
        RETURN QUERY --<< HERE
           SELECT pd_product_comment.*, pd_ion_users.username as username, pd_ion_users.username as username
            FROM pd_product_comment
            LEFT JOIN pd_ion_users ON pd_ion_users.id = pd_product_comment.user_id
            WHERE pd_product_comment.approved_status = 'N'
            GROUP by pd_product_comment.id, pd_product_comment.product_id, username
            ORDER BY pd_product_comment.product_id, pd_product_comment.created_at asc;
      end
    $$;
    See the manual for details: https://www.postgresql.org/docs/curr...ENTS-RETURNING

    But again: using a SQL function is the much better choice for this kind of functions.

    PL/pgSQL is indeed more powerful indeed, but that comes with a price.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #5
    Join Date
    Oct 2016
    Posts
    18
    Provided Answers: 1
    Thank you for explanations!
    Let me one more similar question :
    I want to write function for recalculating of rating of products by comments , like:
    Code:
    CREATE FUNCTION pd_recalc_product_rating(p_id integer, p_write_to_product bool) RETURNS TABLE ( comment_rating_count integer, comment_rating_sum integer )
        LANGUAGE plpgsql
        AS $$
    DECLARE comment_rating_count integer;
    DECLARE comment_rating_sum integer;
       begin
    
           SELECT count("id"), sum(rating) INTO comment_rating_count, comment_rating_sum FROM pd_product_comment WHERE product_id = p_id LIMIT 1;
    
           IF ( p_write_to_product ) THEN
              UPDATE pd_product  SET voting_count = comment_rating_count, voting_summary= comment_rating_sum WHERE product_id = p_id;
           END IF;
    
           SELECT comment_rating_count, comment_rating_sum;
    
        end
    $$;
    But calling :
    Code:
     select *from pd_recalc_product_rating(1, false);
    I got error :
    Code:
    SQL Error [42601]: ERROR: query has no destination for result data
      Hint: If you want to discard the results of a SELECT, use PERFORM instead.
      Where: PL/pgSQL function pd_recalc_product_rating(integer,boolean) line 18 at SQL statement
      org.postgresql.util.PSQLException: ERROR: query has no destination for result data
      Hint: If you want to discard the results of a SELECT, use PERFORM instead.
      Where: PL/pgSQL function pd_recalc_product_rating(integer,boolean) line 18 at SQL statement
    Is only plpgsql good for this? not sql?
    How to fix error ?

  7. #6
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Again: you need RETURN QUERY if you want to return the result of a query from a PL/pgSQL function as I have shown in my second example
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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