Postgres Pro
Downloads
Home   >   mailing lists

Thread: ERROR: SELECT query has no destination for result data

ERROR: SELECT query has no destination for result data

From
"Ezequias Rodrigues da Rocha"
Date:
Hi list,<br /><br />I have a function like this:<br /><br /><font size="1"><span style="font-family:
arial,sans-serif;">CreateOR REPLACE Function base.inserirPontos(char(1), varchar(255), numeric(12,2), int8, int8, int8
)returns int4 as </span><br style="font-family: arial,sans-serif;" /><span style="font-family:
arial,sans-serif;">$$</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family:
arial,sans-serif;">declare</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family:
arial,sans-serif;">   Operacao alias for $1;</span><br style="font-family: arial,sans-serif;" /><span
style="font-family:arial,sans-serif;">    Numero_nota alias for $2;</span><br style="font-family: arial,sans-serif;"
/><spanstyle="font-family: arial,sans-serif;">    Valor_nota alias for $3;</span><br style="font-family:
arial,sans-serif;"/><span style="font-family: arial,sans-serif;">    PontoVenda_Emissor alias for $4;</span><br
style="font-family:arial,sans-serif;" /><span style="font-family: arial,sans-serif;">    Cardpass alias for
$5;</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family: arial,sans-serif;">    Cx_Id alias for
$6;</span><spanstyle="font-family: arial,sans-serif;"> </span><br style="font-family: arial,sans-serif;" /><br
style="font-family:arial,sans-serif;" /><span style="font-family: arial,sans-serif;">begin</span><br
style="font-family:arial,sans-serif;" /><br style="font-family: arial,sans-serif;" /><span style="font-family:
arial,sans-serif;">--Validando parâmetros passados na função</span><br style="font-family: arial,sans-serif;" /><br
style="font-family:arial,sans-serif;" /><span style="font-family: arial,sans-serif;">   if Operacao <> 'C'
then</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family: arial,sans-serif;">     return
1;</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family: arial,sans-serif;">    else</span><br
style="font-family:arial,sans-serif;" /><span style="font-family: arial,sans-serif;">     select count(id) as
numRegistrosfrom base.emissor_ponto_venda where id = PontoVenda_Emissor; </span><br style="font-family:
arial,sans-serif;"/><span style="font-family: arial,sans-serif;">     if numRegistros = 0 then</span><br
style="font-family:arial,sans-serif;" /><span style="font-family: arial,sans-serif;">        return 2;         
</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family: arial,sans-serif;">      else</span><br
style="font-family:arial,sans-serif;" /><span style="font-family: arial,sans-serif;">        insert into base.pontos
values(nextval('base.ponto_id'), now(), Operacao, Numero_nota, Valor_nota, PontoVenda_Emissor, CartaoId(Cardpass),
Cx_id);</span><br style="font-family: arial,sans-serif;" /><span style="font-family: arial,sans-serif;">        return
0;  </span><br style="font-family: arial,sans-serif;" /><span style="font-family: arial,sans-serif;">     end if;
</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family: arial,sans-serif;">  end if;</span><br
style="font-family:arial,sans-serif;" /><span style="font-family: arial,sans-serif;">end</span><br style="font-family:
arial,sans-serif;"/><br style="font-family: arial,sans-serif;" /><span style="font-family:
arial,sans-serif;">$$</span><brstyle="font-family: arial,sans-serif;" /><span style="font-family:
arial,sans-serif;"> LANGUAGE'plpgsql'</span><br style="font-family: arial,sans-serif;" /><br style="font-family:
arial,sans-serif;"/><span style="font-family: arial,sans-serif;">And I am getting the following result when I try to
insertusing the function:</span></font><br /><br /><span style="font-weight: bold;"> select base.inserirPontos('C',
'123456789',12.5, 1, 9877000000944005, 104)</span><br /><br /><br /><span style="color: rgb(255, 0, 0);">ERROR:  SELECT
queryhas no destination for result data</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0,
0);">HINT: If you want to discard the results, use PERFORM instead.</span><br style="color: rgb(255, 0, 0);" /><span
style="color:rgb(255, 0, 0);">CONTEXT:  PL/pgSQL function "inserirpontos" line 17 at SQL statement <br /><br />What I
didwrong ? Any suggestion is quite good please.<br /><br />ps: <br style="color: rgb(255, 0, 0);" /></span><br />-- <br
/>=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<br
/>                                  Atenciosamente(Sincerely) <br />                        Ezequias Rodrigues da
Rocha<br/> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<br />A pior das democracias ainda é
melhordo que a melhor das ditaduras<br />The worst of democracies is still better than the better of dictatorships <br
/><ahref="http://ezequiasrocha.blogspot.com/">http://ezequiasrocha.blogspot.com/</a>

Re: ERROR: SELECT query has no destination for result data

From
John DeSoi
Date:
On Aug 31, 2006, at 9:00 AM, Ezequias Rodrigues da Rocha wrote:

> select count(id) as numRegistros from base.emissor_ponto_venda  
> where id = PontoVenda_Emissor;


declare numRegistros as an integer in the declarations section and  
rewrite the select:

select into numRegistros count(id) from base.emissor_ponto_venda  
where id = PontoVenda_Emissor;

See http://www.postgresql.org/docs/8.1/interactive/plpgsql- 
statements.html#PLPGSQL-SELECT-INTO




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Re: ERROR: SELECT query has no destination for result data

From
"2000 Informatica"
Date:
 
Ezequias,
 
Declare a variavel  numRegistros dentro da funcao e altere o SELECT p/
 
     select count(id) INTO numRegistros from base.emissor_ponto_venda where id = PontoVenda_Emissor;
 
q vai dar certo, OK ??
 
 
SPS
2000Info
 

----- Original Message -----
Sent: Thursday, August 31, 2006 10:00 AM
Subject: [SQL] ERROR: SELECT query has no destination for result data

Hi list,

I have a function like this:

Create OR REPLACE Function base.inserirPontos(char(1), varchar(255), numeric(12,2), int8, int8, int8 ) returns int4 as
$$
declare
    Operacao alias for $1;
    Numero_nota alias for $2;
    Valor_nota alias for $3;
    PontoVenda_Emissor alias for $4;
    Cardpass alias for $5;
    Cx_Id alias for $6;

begin

-- Validando parâmetros passados na função

  if Operacao <> 'C' then
     return 1;
   else
     select count(id) as numRegistros from base.emissor_ponto_venda where id = PontoVenda_Emissor;
     if numRegistros = 0 then
        return 2;         
      else
        insert into base.pontos values (nextval('base.ponto_id'), now(), Operacao, Numero_nota, Valor_nota, PontoVenda_Emissor, CartaoId(Cardpass), Cx_id);
        return 0;  
     end if;
  end if;
end

$$
 LANGUAGE 'plpgsql'

And I am getting the following result when I try to insert using the function:

select base.inserirPontos('C', '123456789', 12.5, 1, 9877000000944005, 104)


ERROR:  SELECT query has no destination for result data
HINT:  If you want to discard the results, use PERFORM instead.
CONTEXT:  PL/pgSQL function "inserirpontos" line 17 at SQL statement

What I did wrong ? Any suggestion is quite good please.

ps:

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/434 - Release Date: 30/8/2006

Re: ERROR: SELECT query has no destination for result data

From
John DeSoi
Date:
Sorry, try

select into numRegistros count(*) from base.emissor_ponto_venda where
id = PontoVenda_Emissor;

The thing you are selecting into needs to match what you are
selecting. So in your examples below, record is needed when you use
"* from ...". If you just need to look at one value:

select into num_em_pdv id from base.emissor_ponto_venda where id =
PontoVenda_Emissor;


John


On Aug 31, 2006, at 10:07 AM, Ezequias Rodrigues da Rocha wrote:

> Thank you John,
>
> It only works using records. I don't know why.
>
> When I put ('works well'):
> select into num_em_pdv * from base.emissor_ponto_venda where id =
> PontoVenda_Emissor;
>          if num_em_pdv.id is null then --> Se o emissor ponto venda
> passado não tem na base retorne 4
>             retorno:= 4;
>
> When I put (don't works well):
>     num_em_pdv int4;
> select into num_em_pdv * from base.emissor_ponto_venda where id =
> PontoVenda_Emissor;
>          if num_em_pdv.id is null then --> Se o emissor ponto venda
> passado não tem na base retorne 4
>             retorno:= 4;
>
> Reports the error:
> ERROR:  missing FROM-clause entry for table "num_em_pdv"
> CONTEXT:  SQL statement "SELECT  num_em_pdv.id is null"
> PL/pgSQL function "inserirpontos" line 30 at if
>
> Thank you John. I think I should stay with the record type.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Re: ERROR: SELECT query has no destination for result data

From
Joe
Date:
Ezequias Rodrigues da Rocha wrote:
> Hi list,
>
> I have a function like this:
>
> Create OR REPLACE Function base.inserirPontos(char(1), varchar(255), 
> numeric(12,2), int8, int8, int8 ) returns int4 as
> $$
> declare
>     Operacao alias for $1;
>     Numero_nota alias for $2;
>     Valor_nota alias for $3;
>     PontoVenda_Emissor alias for $4;
>     Cardpass alias for $5;
>     Cx_Id alias for $6;
>
> begin
>
> -- Validando parâmetros passados na função
>
>   if Operacao <> 'C' then
>      return 1;
>    else
>      select count(id) as numRegistros from base.emissor_ponto_venda 
> where id = PontoVenda_Emissor;
You haven't declared numRegistros.

Joe