query has no destination for result data

Question

i have table like below with name student

     S.No    Name 
      1.    Ramesh
      2.    Raju
      3.    SOmu
    -------------
    -------------  etc

My requirement is when i am passing the S.no i will get the Name by using function

My function like below:

CREATE OR REPLACE FUNCTION fn_name(v_sno bigint)
  RETURNS TEXT
  AS
$BODY$

    DECLARE RESULT  VARCHAR(5000);

    BEGIN   
        SELECT  RESULT  "Name"  FROM "Student" WHERE "s.no" = v_sno;

        RETURN RESULT;
     END;

 $BODY$
  LANGUAGE plpgsql
  COST 100;
ALTER FUNCTION fn_name(bigint)
  OWNER TO postgresql

but i am getting the following error .

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function fn_name(bigint) line 6 at SQL statement

please help me how to resolve the above issue.

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW — , May 15 '14 at 07:16
Hello I've never seen the object in which you wish to store the data shoe horned into a sql statement like that. (SELECT RESULT "Name") — John Hogan, May 15 '14 at 07:19
What's wrong with manual? Just copy/paste one of the examples, change it a little bit and you're done. — Frank Heikens, May 15 '14 at 07:20
CREATE OR REPLACE FUNCTION fn_name(v_sno bigint) RETURNS TEXT AS $BODY$ DECLARE RESULT VARCHAR(5000); BEGIN SELECT "Name" into RESULT FROM "Student" WHERE "s.no" = v_sno; RETURN RESULT; END; $BODY$ LANGUAGE plpgsql COST 100; ALTER FUNCTION fn_name(bigint) OWNER TO postgresql — user3639616, May 15 '14 at 07:59

Craig Ringer · Answer 1 · 2014-05-15T11:49:36.687

The immediate question is easily answered by the manual (see SELECT INTO).

However, all that is a rather overcomplicated way to do what you want. Things notably wrong:

varchar(5000) is weird and unnecessary. Use text or unqualified varchar.
SELECT RESULT "Name" doesn't do what you probably think it does. That's trying to SELECT a column named result and alias it as Name. I think you were looking for SELECT INTO.
"s.no" means the column named s.no. Is that what you meant? Because that's a horrid name for a column; without the quotes it'd be interpreted as the column no from the table s. Don't use periods in column names or table names.

All you really need is:

CREATE OR REPLACE FUNCTION fn_name(bigint)
RETURNS TEXT AS
$BODY$
SELECT "Name" FROM "Student" WHERE "s.no" = $1;
$BODY$
LANGUAGE sql;

(assuming "s.no" really is your column name, not a mangled attempt at an alias).

If you had to use PL/PgSQL for this simple job for some reason (maybe you intend to make it more complex later), you'd write:

CREATE OR REPLACE FUNCTION fn_name(v_sno bigint)
RETURNS TEXT AS
$BODY$
DECLARE
    result text;
BEGIN
    SELECT INTO result "Name" FROM "Student" WHERE "s.no" = $1;
    RETURN result;
END;
$BODY$
LANGUAGE plpgsql;

ALTER FUNCTION fn_name OWNER TO postgresql – user3639616 Apr 28 '16 at 07:23 — user3639616, Apr 28 '16 at 07:23

query has no destination for result data

1 Answers1

Linked