Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 6E45F1337B39 for ; Tue, 1 Mar 2011 03:56:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 74889-02 for ; Tue, 1 Mar 2011 07:55:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og125.obsmtp.com (exprod7og125.obsmtp.com [64.18.2.28]) by mail.postgresql.org (Postfix) with ESMTP id 95F59133616B for ; Tue, 1 Mar 2011 03:55:55 -0400 (AST) Received: from source ([209.85.210.170]) (using TLSv1) by exprod7ob125.postini.com ([64.18.6.12]) with SMTP ID DSNKTWyminiJwKLHAr8alro/8ll599Iw9u2p@postini.com; Mon, 28 Feb 2011 23:55:56 PST Received: by iyb12 with SMTP id 12so6837029iyb.1 for ; Mon, 28 Feb 2011 23:55:54 -0800 (PST) Received: by 10.42.85.72 with SMTP id p8mr5448761icl.47.1298966154349; Mon, 28 Feb 2011 23:55:54 -0800 (PST) Received: from [192.168.1.2] ([122.169.79.60]) by mx.google.com with ESMTPS id z4sm5431425ibg.7.2011.02.28.23.55.51 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 28 Feb 2011 23:55:53 -0800 (PST) Subject: Re: what's wrong in this procedure? Mime-Version: 1.0 (Apple Message framework v1082) Content-Type: text/plain; charset=iso-8859-1 From: Vibhor Kumar In-Reply-To: <080608d1-b5ea-4b40-8042-f21867f97cc8@glegroupsg2000goo.googlegroups.com> Date: Tue, 1 Mar 2011 13:26:34 +0530 Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <080608d1-b5ea-4b40-8042-f21867f97cc8@glegroupsg2000goo.googlegroups.com> To: pgsql.sql@googlegroups.com X-Mailer: Apple Mail (2.1082) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.2 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3 X-Spam-Level: X-Archive-Number: 201103/1 X-Sequence-Number: 35203 On Feb 25, 2011, at 11:16 PM, Camaleon wrote: > This error is returned Erro de SQL: >=20 > ERROR: column "Aguardando Pagto" does not exist at character 352 >>> >=20 >=20 > create or replace function get_historico() RETURNS SETOF = twiste.type_cur__historico AS ' >=20 > SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) = AS transacoes > FROM ofertas o > JOIN transacao t ON o.ofertas_id =3D t.ofertas_id > JOIN municipio m ON o.municipio_id =3D m.municipio_id > WHERE o.data_fim <=3D now() AND t.status IN("Aguardando Pagto", "Em = an=E1lise", "Aprovado", "Completo") > GROUP BY o.data_fim; > ' > language 'sql'; try Following: create or replace function get_historico() RETURNS SETOF = twiste.type_cur__historico AS $$ SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS = transacoes FROM ofertas o JOIN transacao t ON o.ofertas_id =3D t.ofertas_id JOIN municipio m ON o.municipio_id =3D m.municipio_id WHERE o.data_fim <=3D now() AND t.status IN('Aguardando Pagto', 'Em = an=E1lise', 'Aprovado', 'Completo') GROUP BY o.data_fim; $$ language 'sql'; Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com