Postgres Function returning no results but when the same query returns results outside function

Question

I am running a simple postgres function to return the count of rows. I am able to run the same query outside function with the output of raise option , but the function doesn't return any rows. I have tried different ways to produce results but unable to. Please find my function below,

CREATE OR REPLACE FUNCTION my_schema.usp_spellcheck3(SearchORItems_WithPipe varchar, site varchar, lan varchar, rows_display integer)
  RETURNS TABLE (docmnt int) AS $BODY$
DECLARE
  arrSearchTerms text[];
  NewTerm varchar;
  i integer;
  AltSearch_withComma varchar;
  AltSearch_withPipe varchar;
  strDidYouMean varchar;
  dpDidYouMean double precision;
  txtDidYouMean Text;
  SearchORItems_withComma  varchar;
  SearchORItems  varchar;
  SearchORItem  varchar;
  ws varchar;
  arrSearchORItems_withComma varchar[];
BEGIN
  strDidYouMean = 'DidYouMeanRow';
  dpDidYouMean = 0.0;
  txtDidYouMean = 'DidYouMeanRow';

  ws = '''' || '%' || site || '%' || '''' ;
  RAISE NOTICE '%', ws;

  SearchORItems = REPLACE(SearchORItems_WithPipe, '|', ',');
  SELECT regexp_split_to_array(SearchORItems, ',') INTO arrSearchORItems_withComma;
  RAISE NOTICE '%', SearchORItems;

  FOR i IN 1 .. coalesce(array_upper(arrSearchORItems_withComma, 1), 1) LOOP
    IF (i = 1) THEN
      SearchORItems_withComma = '''' || arrSearchORItems_withComma[i] || '''';
    ELSE
      SearchORItems_withComma = SearchORItems_withComma||','||'''' || arrSearchORItems_withComma[i] || '''';
    END IF;
  END LOOP;
  RAISE NOTICE '%',SearchORItems_withComma;

  SELECT COUNT(*) INTO res_count 
  FROM ( 
    SELECT 1 FROM my_schema.features f , my_schema.documents d 
    WHERE term IN (SearchORItems_withComma) 
      AND d.docid = f.docid
      AND d.url LIKE ws
    GROUP BY f.docid, d.url) t; 

  RAISE NOTICE '%', res_count;

  SearchORItem = 'SELECT COUNT(*) INTO res_count
    FROM (SELECT 1 FROM my_schema.features f , my_schema.documents d 
    WHERE term IN ('||SearchORItems_withComma||') 
    AND d.docid = f.docid AND d.url LIKE ' || ws ||'
    GROUP BY f.docid, d.url) t';

  RAISE NOTICE '%',SearchORItem;
END;
$BODY$ LANGUAGE SQL VOLATILE;

this is my query output : NOTICE: '%uni%' NOTICE: daniel,data NOTICE: 'daniel','data' NOTICE: 0 NOTICE: select count(*) into res_count from ( select 1 from my_schema.features f , my_schema.documents d where term in ('daniel','data') and d.docid=f.docid and d.url like '%uni%' group by f.docid,d.url)t

Total query runtime: 16 ms. 0 rows retrieved.

I dont know where I'm going wrong, any help would be appreciated .. Thanks..

Patrick Patrick · Accepted Answer · 2015-01-25T03:38:43

The simple reason that nothing is returned is that you have no RETURN statements in your code. When a function RETURNS TABLE you need to explicitly put one or more RETURN NEXT or RETURN QUERY statements in the body of your code, with a final RETURN statement to indicate the end of the function. See the documentation here: http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING. What exactly you want to return is not clear but likely candidates are res_count and d.docid.

Other than that, your code could use a real clean-up reducing clutter like:

ws := '''%' || site || '%''' ;

instead of:

ws = '''' || '%' || site || '%' || '''' ;

and:

SELECT COUNT(*) INTO res_count 
FROM my_schema.features f,
JOIN my_schema.documents d ON d.docid = f.docid
WHERE term IN (SearchORItems_withComma) 
  AND d.url LIKE ws
GROUP BY f.docid, d.url;

instead of:

SELECT COUNT(*) INTO res_count 
FROM ( 
  SELECT 1 FROM my_schema.features f , my_schema.documents d 
  WHERE term IN (SearchORItems_withComma) 
    AND d.docid = f.docid
    AND d.url LIKE ws
  GROUP BY f.docid, d.url) t;

And you should use the assignment operator (:=) instead of the equality operator in any plpgsql statement that is not a SQL statement.

Postgres Function returning no results but when the same query returns results outside function

1 Answers