Re: Dynamic SQL in function

robert.goodwin@ums.msfc.nasa.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...