PostgreSQL:Error while calling a custom function

Question

Following is a Function created in PostgreSQL 9.2 ,

CREATE OR REPLACE FUNCTION getRows()
RETURNS TABLE (
        "Name" character varying
       ,"Address" text
       ,"Phone" text
       ,"Email" character varying
       ,"Tin" character varying
       ,"DL.No1, DL.No2" text
       ,"Area" character varying
) AS
$func$
BEGIN
       select  acname as "Name", coalesce(nullif(concat_ws(', ', nullif(add1, ''),  
       nullif(add2, '')), ''), 'NIL') "Address", coalesce(nullif(concat_ws(', ', 
       nullif(phoneoff, ''), nullif(mobile, '')), ''), 'NIL') "Phone",case when email 
       <>'' then email else 'NIL' end as "Email",case when email <>'' then tin else 
       'NIL' end as "Tin",case when dlno1 ||', '||dlno2=', ' then 'NIL' else dlno1 ||',  
       '||dlno2 end as "DL.No1, DL.No2",areaname as "Area" from gtab12   left  join 
      gtab47 using(areaid) where acgrcode  = '204'  order by areaname,"Name";
END
$func$  LANGUAGE plpgsql;

When select getRows() an error is occurring

     ERROR:  query has no destination for result data
      HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
   CONTEXT:  PL/pgSQL function getRows() line 4 at SQL statement

given below is actually what the select query inside the function getRow returns. enter image description here

pozs · Accepted Answer · 2014-08-25 15:21:15Z

2

You cannot use a SELECT statement in a plpgsql function without the INTO clause (you must use PERFORM instead).

You also forget to RETURN with something.

But, if your function is that simple, you could use a plain sql function (or even a view) instead.

answered Aug 25, 2014 at 15:21

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

harmic · Accepted Answer · 2014-08-25 12:38:46Z

1

To return a set of something, you need to use RETURN NEXT (to return a row at a time) or RETURN QUERY (to return a number of rows resulting from a query).

See the manual section relating to returning values.

Your example function would need to have:

RETURN QUERY SELECT....;
RETURN;

Note the need for the second RETURN - this is needed because there may be multiple RETURN QUERY statements before the function exits.

answered Aug 25, 2014 at 12:38

harmic

31k6 gold badges75 silver badges102 bronze badges

Collectives™ on Stack Overflow

PostgreSQL:Error while calling a custom function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related