Re: Dynamic SQL in function

robert(dot)goodwin(at)ums(dot)msfc(dot)nasa(dot)gov (Robert Goodwin) writes:

> I'm trying to define a simple function that uses dynamic SQL. Here is
> the function definition:
>
> CREATE OR REPLACE FUNCTION count_records (VARCHAR)
> RETURN INTEGER AS '
> DECLARE
> cursor1 REFCURSOR;
> num_recs INTEGER;
> BEGIN
> OPEN cursor1 FOR EXECUTE "SELECT count(*) FROM " ||

Don't use double quotes. Use doubled single quotes:

OPEN cursor1 FOR EXECUTE ''SELECT count(*) FROM '' || ...

Double quotes always delimit an identifier.

-Doug
--
Doug McNaught Wireboard Industries http://www.wireboard.com/

Custom software development, systems and network consulting.
Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...