Обсуждение: How can I test a function in the SQL window?
<div class="WordSection1"><p class="MsoNormal">Hello!<p class="MsoNormal"> <p class="MsoNormal">I suddenly find myself
stumped. A co-worker gave me a function to use in my database, but it doesn’t seem to be doing anything. So, I wanted
torun it from PGAdmin’s SQL window. But I can’t call it. When I try<p class="MsoNormal"> <p class="MsoNormal">select
standupdatestatus('12','Loaded', 100);<p class="MsoNormal"> <p class="MsoNormal">I get:<p class="MsoNormal"> <p
class="MsoNormal">ERROR: query has no destination for result data<p class="MsoNormal">HINT: If you want to discard the
resultsof a SELECT, use PERFORM instead.<p class="MsoNormal">CONTEXT: PL/pgSQL function
standupdatestatus(text,text,integer)line 44 at SQL statement<p class="MsoNormal">********** Error **********<p
class="MsoNormal"> <pclass="MsoNormal">ERROR: query has no destination for result data<p class="MsoNormal">SQL state:
42601<pclass="MsoNormal">Hint: If you want to discard the results of a SELECT, use PERFORM instead.<p
class="MsoNormal">Context:PL/pgSQL function standupdatestatus(text,text,integer) line 44 at SQL statement<p
class="MsoNormal"> <pclass="MsoNormal">When I try<p class="MsoNormal"> <p class="MsoNormal">perform
standupdatestatus('12','Loaded', 100);<p class="MsoNormal"> <p class="MsoNormal">I get:<p class="MsoNormal"> <p
class="MsoNormal">ERROR: syntax error at or near "perform"<p class="MsoNormal">LINE 1: perform standupdatestatus('12',
'Loaded',100);<p class="MsoNormal"> ^<p class="MsoNormal">********** Error **********<p class="MsoNormal"> <p
class="MsoNormal">ERROR:syntax error at or near "perform"<p class="MsoNormal">SQL state: 42601<p
class="MsoNormal">Character:1<p class="MsoNormal"> <p class="MsoNormal">What do I need to do?<p class="MsoNormal"> <p
class="MsoNormal">Thanksvery much!<p class="MsoNormal"> <p class="MsoNormal">RobR</div>
On 02/04/2015 18:02, Rob Richardson wrote:
> Hello!
>
>
>
> I suddenly find myself stumped. A co-worker gave me a function to use
> in my database, but it doesn’t seem to be doing anything. So, I wanted
> to run it from PGAdmin’s SQL window. But I can’t call it. When I try
>
>
>
> select standupdatestatus('12', 'Loaded', 100);
>
>
>
> I get:
>
>
>
> 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 standupdatestatus(text,text,integer) line 44
> at SQL statement
>
> ********** 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 standupdatestatus(text,text,integer) line 44
> at SQL statement
>
>
>
> When I try
>
>
>
> perform standupdatestatus('12', 'Loaded', 100);
>
>
>
> I get:
>
>
>
> ERROR: syntax error at or near "perform"
>
> LINE 1: perform standupdatestatus('12', 'Loaded', 100);
You probably need to do:
perform * from standupdatestatus(...);
HTH,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Hello!
I suddenly find myself stumped. A co-worker gave me a function to use in my database, but it doesn’t seem to be doing anything. So, I wanted to run it from PGAdmin’s SQL window. But I can’t call it. When I try
select standupdatestatus('12', 'Loaded', 100);
I get:
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 standupdatestatus(text,text,integer) line 44 at SQL statement
********** 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 standupdatestatus(text,text,integer) line 44 at SQL statement
The error is telling you that the query at line 44 inside your function either needs to be called using PERFORM or needs be modified to output its result somewhere (INTO variable; FOR record IN SELECT; etc...) or cause the query result to be returned from the function using RETURN QUERY.
When I try
perform standupdatestatus('12', 'Loaded', 100);
I get:
ERROR: syntax error at or near "perform"
LINE 1: perform standupdatestatus('12', 'Loaded', 100);
^
See my first comment. PERFORM is a pl/pgsql command, not an SQL one. In SQL you only ever use SELECT and the results always have a destination - the client. Since functions are black-boxes there is no "client" to send plain query results back to so you either need to do something with them or indicate you are executing the statement solely for its side effects - via PERFORM.
David J.
On Thu, Apr 2, 2015 at 1:17 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Thu, Apr 2, 2015 at 10:02 AM, Rob Richardson <RDRichardson@rad-con.com>
> wrote:
>>
>> Hello!
>>
>>
>>
>> I suddenly find myself stumped. A co-worker gave me a function to use in
>> my database, but it doesn’t seem to be doing anything. So, I wanted to run
>> it from PGAdmin’s SQL window. But I can’t call it. When I try
>>
>>
>>
>> select standupdatestatus('12', 'Loaded', 100);
>>
>>
>>
>> I get:
>>
>>
>>
>> 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 standupdatestatus(text,text,integer) line 44
>> at SQL statement
>>
>> ********** 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 standupdatestatus(text,text,integer) line 44 at
>> SQL statement
>>
>>
>
>
> The error is telling you that the query at line 44 inside your function
> either needs to be called using PERFORM or needs be modified to output its
> result somewhere (INTO variable; FOR record IN SELECT; etc...) or cause the
> query result to be returned from the function using RETURN QUERY.
>
>>
>> When I try
>>
>>
>>
>> perform standupdatestatus('12', 'Loaded', 100);
>>
>>
>>
>> I get:
>>
>>
>>
>> ERROR: syntax error at or near "perform"
>>
>> LINE 1: perform standupdatestatus('12', 'Loaded', 100);
>>
>> ^
>>
>>
>
> See my first comment. PERFORM is a pl/pgsql command, not an SQL one. In
> SQL you only ever use SELECT and the results always have a destination - the
> client. Since functions are black-boxes there is no "client" to send plain
> query results back to so you either need to do something with them or
> indicate you are executing the statement solely for its side effects - via
> PERFORM.
>
> David J.
>
From the sound of it the function you've been giving is performing a
SELECT within the function body and not assigning the result of that
select to anything. You may want to post the body of the function so
for further assistance.(even though this is really seems like a
question better suited for the pgsql-general list)
Leonard