[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, 987700944005, 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 ditadurasThe worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
Re: [SQL] ERROR: SELECT query has no destination for result data
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Checking types
Hi list,I would like to check if the types passed are of the same time of my declaration and not report an error.Is that possible ?How to do that ?is there any statement like is numeric or is varchar(12) ? RegardsEzequias
Re: [SQL] ERROR: SELECT query has no destination for result data
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 -
From:
Ezequias Rodrigues da Rocha
To: pgsql-sql@postgresql.org
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,
987700944005, 104)ERROR: SELECT query has no destination for
result dataHINT: 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 ditadurasThe 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: [SQL] ERROR: SELECT query has no destination for result data
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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Trigger on Insert to Update only newly inserted fields?
On 8/28/06, Henry Ortega <[EMAIL PROTECTED]> wrote: Here's what I am doing:I have this table:employee payrate effective tstamp end_date (to be updated by trigger)jdoe 1000 04-01-2006 2006-03-10 13:39: 07.614945jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444 peter 500 04-1-2006 2006-03-25 08:13:35.152166 peter 900 04-16-2006 2006-03-28 09:22:14.456221 After the trigger runs, I want to have this:employee payrate effective tstamp end_date (to be updated by trigger) jdoe 1000 04-01-2006 2006-03-10 13:39:07.614945 04-15-2006 jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325 04-15-2006 jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444 NULL peter 500 04-1-2006 2006-03-25 08:13:35.152166 04-15-2006 peter 900 04-16-2006 2006-03-28 09:22:14.456221 NULL You may want to separate this into two tables:employee_day employee_day_id bigserial employee_name varchar(100) effective_day dateeffective_pay effective_pay_id bigserial tstamp timestamp employee_day_id (foreign key)Then you can calculate end_day off the employee_day table (you can do this without splitting out the table however splitting the table means fewer records for this query and a more normalized database): SELECT ed1.employee_day_id, ed1.employee_name, ed1.effective_day, case ed2.effective_day when null then null else min(ed2.effective_day) - interval '1 day' end FROM employee_day ed1LEFT OUTER JOIN employee_day ed2 ON ( ed1.employee_name = ed2.employee_name AND ed1.effective_day < ed2.effective_day)Since it appears end_date is a derived value, you can use the above query as a view. I guess there could be performance concerns but then you could create a materialized view for it. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] ERROR: SELECT query has no destination for result data
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Trigger with Stored Procedure [Client Points]
On 8/30/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list,I have a table like this:Points-Idoperationvalueand another two tables like thisClient Rule ---id idname percent Points ps: The Points table is a table that represent each moviment of the Client.I need to make a trigger that see after all insert on Points table and update the client table based on the percent on the Rule table. Anyone that would like to help me I will be very welcomed. I am not exactly sure what you are trying to do here. Can you provide an example of the data and what you want to occur? == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
[SQL] How to get the row that start a trigger
Hi,I need make a trigger that update a table each time another table has a insert. but this insert has on this row all the items to pass to my function on the trigger.How to retrieve this information. Please I need this answare.RegardsEzequias-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-A pior das democracias ainda é melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Trigger with Stored Procedure [Client Points]
On 8/31/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Bono,I must check each insert on my Points(each shop generate a new line on Points table).When I insert the record on points I must see the field Value(on points table already) and update the Client.Points field based in some rule. My fear is not get correct row and do not update the correct client.Any help would be very welcome.RegardsEzequias2006/8/31, Aaron Bono < [EMAIL PROTECTED]>: On 8/30/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list,I have a table like this:Points-Idoperationvalueand another two tables like thisClient Rule ---id idname percent Points ps: The Points table is a table that represent each moviment of the Client.I need to make a trigger that see after all insert on Points table and update the client table based on the percent on the Rule table. Anyone that would like to help me I will be very welcomed. I am not exactly sure what you are trying to do here. Can you provide an example of the data and what you want to occur? Please include the list when you reply (reply to all).So are you saying you need to add foreign keys to the tables? Or are the rules what dictate what records should be updated? From what you have said and what I can see of your tables, the rule and points tables need foreign keys to the clients table so you know what clients the points and rules are for. Of course I am guessing based off the very limited information you have provided. If we are to help you, you really need to provide some examples. What kind of rules are you talking about?== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
