Thread: SELECT is causing a runtime error when used in stored functions: State=42601, Err=7, Msg=ERROR: query has no destination for result data;

It would be great, if you give me a hint on a problem I am having.

 

When I am using "SELECT" in a prepared function called through ODBC / C++ interface, I am getting a runtime Error:

 

State=42601, Err=7, Msg=ERROR: query has no destination for result data;

 

Example:

CREATE OR REPLACE FUNCTION MyTest

(

      IN OUT      v_1 int

 

) AS $$BEGIN

 

      SELECT v_1 = 1;

 

END;$$ LANGUAGE plpgsql

 

 

I can get rid of the error message, when I am changing to Postgres Syntax

      v_1 := 1;

 

However I have no solution for selecting several vars from a table:

Example (which works without problem in MS SQL and MySQL):

 

      SELECT v_ps = sex,

             v_pl = lng,

             v_pa = age,

             v_pr = reg,

             v_pg = grp

         FROM pref

         WHERE memb_id = v_id;

 

How can I get rid of the "query has no destination for result data"?

 

Your help is much appreciated.

 

Kindest regards

 

 

Lothar Bongartz

 

On 16/01/2010 13:47, Lothar Bongartz wrote:
> State=42601, Err=7, Msg=ERROR: query has no destination for result data;
> Example:
>
> CREATE OR REPLACE FUNCTION MyTest
> (
>       IN OUT      v_1 int
> ) AS $$BEGIN
>       SELECT v_1 = 1;
> END;$$ LANGUAGE plpgsql

The error message says it all - in plpgsql you need to specify a
destination variable for the result of the SELECT.

  create....
  as
  $$
  declare
    m_result integer;
  begin
    select 1 into m_result;
    return m_result;
  end;
  $$
  language plpgsql;

If you use an OUT parameter as you did in your example, you don't need
the "return m_result;", though you might still need a "return;" to end
the function - not sure on this one.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie