Thread: pl/pgsql frustration

Guys,

I'm trying to write a simple function that will return a table's actual
row count on PostgreSQL 7.4.  I've tried two different versions (see
below), both with the same result:

"ERROR:  syntax error at or near "select" at character 127"

I've also tried a cursor example found in the interactive 7.4 docs and
get a similar 'select' error.  Am I missing something here or is
something else going on?  Thanks for any help you can provide.

John
----
create or replace function count_rows (text) returns integer as '
declare
  return_val int4;
  curs1 refcursor;
  table_name ALIAS FOR $1;
begin
    OPEN curs1 FOR EXECUTE 'select * from ' || quote_indent(table_name);

    fetch curs1 into return_val;
    close curs1;
end;
' language 'plpgsql';

create or replace function count_rows (text) returns integer as '
declare
  return_val int4;
  curs1 cursor FOR EXECUTE 'select count(*) from ' ||
quote_indent(table_name);
  table_name ALIAS FOR $1;
begin
    OPEN curs1;
    fetch curs1 into return_val;
    close curs1;
end;
' language 'plpgsql';

Guys,

Sorry...never mind.  I had accidentally pulled up 8.0 documentation and
confused the syntax (single quotes being ok with you're using the $$
quoting, which I'm not).

Thanks,
John

On Sat, Aug 13, 2005 at 11:19:25PM -0400, John Wells wrote:
> I'm trying to write a simple function that will return a table's actual
> row count on PostgreSQL 7.4.  I've tried two different versions (see
> below), both with the same result:
>
> "ERROR:  syntax error at or near "select" at character 127"

You're having quoting problems; see "Handling of Quotation Marks"
in the "Tips for Developing in PL/pgSQL" section of the documentation:

http://www.postgresql.org/docs/7.4/interactive/plpgsql-development-tips.html

PostgreSQL 8.0 simplifies quoting issues by allowing an alternate
style known as "dollar quoting":

http://www.postgresql.org/docs/8.0/interactive/plpgsql-development-tips.html

In addition, the functions you posted have "quote_ident" misspelled
and they don't have RETURN statements.  You might also want to
create the functions as STRICT (aka RETURNS NULL ON NULL INPUT).
See the CREATE FUNCTION documentation for more info:

http://www.postgresql.org/docs/7.4/interactive/sql-createfunction.html

--
Michael Fuhr