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;
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
Re: SELECT is causing a runtime error when used in stored functions: State=42601, Err=7, Msg=ERROR: query has no destination for result data;
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