Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010

    Unanswered: Function returning two different things?

    I have a function defined like so:

    create or replace function DOCSEARCH (p_search_string VARCHAR,
    p_docs_per_page INTEGER,
    p_page_num INTEGER)

    returns setof searchrecords as $$
    r searchrecords%rowtype;
    v_search_string text;
    v_search_string := '('||p_search_string||')'||' & !{ssx0}';

    for r in (select * from (select * from doctable where
    to_tsvector('default',textsearch) @@ to_tsquery('default',v_search_string)
    order by sortfactor)
    limit 20) loop

    return next r;

    end loop;
    $$ language plpgsql;

    It's early days for the code, but it at least works OK. One refinement we want: add in a count of the matching records in total and add an additional search term to v_search_string when a large count is found. However, we'd then want to alert users that a 'large search restriction' is in place. So, not only do they get the records returned, they also get a message returned. By the same function.

    Question is, therefore, how do I re-write this function so that it not only returns a "setof searchrecords", but ALSO and only *sometimes* returns a text string (which will contain my alert message about 'large count found')?

    I'm more used to Oracle procedures, in which I'd declare something like :

    create function X (p_search_string IN VARCHAR2,
    p_docs_per_page IN NUMBER,
    p_page_num IN NUMBER,
    p_resultset OUT t_refcur,
    r_error_message OUT varchar2)...

    ...and so on. Putting it another way, then, how would I write a PostgreSQL function to handle two OUT parameters?

    All help gratefully received!
    Last edited by dizwell; 06-21-10 at 20:27.

  2. #2
    Join Date
    Aug 2009
    Olympia, WA
    You are always going to have to return a tuple. So you can't return 1 out parameter on some calls and 2 on others.

    Although you didn't specify, I'm guessing that searchrecords is a complex type. Add an error_message column to the searchrecords type. It will then always have to return an error_message column but you'll just test for not null.

  3. #3
    Join Date
    Apr 2010
    I suggest to use RISE NOTICE
    In that case application will get data from function and notice from a server with message you want

Posting Permissions

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