Thread: Count(*) throws error

Hi,

I'm using the following statement in my plpgsql function

SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;   

I have decalred no_rows int4 and initialized it to zero

Running the function throws the following error:

ERROR:  syntax error at or near "(" at character 13
QUERY:  SELECT   $1 (*) FROM tbl_concurrent
CONTEXT:  SQL statement in PL/PgSQL function "sp_insert_tbl_concurrent" near line 8


If i comment this count(*) line, the error goes.

I don't know why isn't count(*) working

Thanks
Jas

On Jul 11, 2007, at 4:00 PM, Jasbinder Singh Bali wrote:

> Hi,
>
> I'm using the following statement in my plpgsql function
>
> SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;
>
> I have decalred no_rows int4 and initialized it to zero
>
> Running the function throws the following error:
>
> ERROR:  syntax error at or near "(" at character 13
> QUERY:  SELECT   $1 (*) FROM tbl_concurrent
> CONTEXT:  SQL statement in PL/PgSQL function
> "sp_insert_tbl_concurrent" near line 8
>
>
> If i comment this count(*) line, the error goes.
>
> I don't know why isn't count(*) working

SELECT COUNT(*) INTO no_rows FROM tbl_concurrent;

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

 -------------- Original message ----------------------
From: "Jasbinder Singh Bali" <jsbali@gmail.com>
> Hi,
>
> I'm using the following statement in my plpgsql function
>
> SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;
>
> I have decalred no_rows int4 and initialized it to zero
>
> Running the function throws the following error:
>
> ERROR:  syntax error at or near "(" at character 13
> QUERY:  SELECT   $1 (*) FROM tbl_concurrent
> CONTEXT:  SQL statement in PL/PgSQL function "sp_insert_tbl_concurrent" near
> line 8
>

Try SELECT COUNT(*) INTO no _rows FROM tbl_concurrent;
See
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
for complete syntax.
> If i comment this count(*) line, the error goes.
>
> I don't know why isn't count(*) working
>
> Thanks
> Jas

On Jul 11, 4:00 pm, jsb...@gmail.com ("Jasbinder Singh Bali") wrote:
> I don't know why isn't count(*) working

Works for me:

create table tbl_concurrent(a int);
insert into tbl_concurrent values (1);
insert into tbl_concurrent values (9);
insert into tbl_concurrent values (4);

create or replace function foo() returns int as
$$
declare
  no_rows int := 0;
begin
  select into no_rows count(*) from tbl_concurrent;
  return no_rows;
end;
$$
language plpgsql;

select foo();

 foo
-----
   3

"Jasbinder Singh Bali" <jsbali@gmail.com> writes:
> I'm using the following statement in my plpgsql function

> SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;

> I have decalred no_rows int4 and initialized it to zero

> Running the function throws the following error:

> ERROR:  syntax error at or near "(" at character 13
> QUERY:  SELECT   $1 (*) FROM tbl_concurrent

I'll bet a nickel you have a local variable named "count" in that
function, and plpgsql is blindly trying to substitute its value into
the SQL query.  The replacement of "COUNT" by " $1 " in the query
text is the tip-off.

            regards, tom lane